Using Excel to Create Timesheets (but dumb ones)

[Sir Topham Hatt]

Someone in my team did the old "use excel because of the boxes" thing years ago and created timesheets using an excel spreadsheet.

These spreadsheets don't do any calculations.

 

I was looking at the IF/THEN command and whether it could have more than one IF?

For example if box A1 says W then box Z1 says WORK, but I also need it to check if box A1 has another letter and if it does, put something else in box Z1 - there may be 5 or 6 potential letters in box A1 and each one would need to print something different in Z1.

 

Is that possible?

 

Not too worried about calculating the hours worked as yet a that should be easy (ish!).

[+BudMan MVC]

Prob be easier to use a vlookup table.  You create a table with values and what you would like it to return.. Then in your z1 cell put in the formula to look up A1 in the table and return what you want to show in Z1.

 

This might help

https://exceljet.net/excel-functions/excel-vlookup-function

[Matthew S.]

  On 03/10/2020 at 15:07, BudMan said:

Prob be easier to use a vlookup table.  You create a table with values and what you would like it to return.. Then in your z1 cell put in the formula to look up A1 in the table and return what you want to show in Z1.

 

This might help

https://exceljet.net/excel-functions/excel-vlookup-function

Expand  

Could also use a 3D array lookup using INDEX / MATCH as well, though this could be costly.

[+BudMan MVC]

Doesn't sound like he has all that much to lookup   Just 6 letters

[Sir Topham Hatt]

Hi

Thanks - have used this function instead and it works fine. Put the codes in a different sheet and added a query to be able to call the codes / display text from that sheet.

 

The next part is hours worked, which seems to involve a long IF equation otherwise it shows minus hours:

 

=IF(E15>C15, E15-C15, 1-C15+E15)

 

Which gives me the result I'm looking for. 

 

I may be back depending on how complicated I want to make it but managed to also add protection for the formulas, so I can't change those cells, while still being able to add hours worked and such. 

 

Cheers

Edited October 8, 2020 by Sir Topham Hatt

[Sir Topham Hatt]

Okay, so here we go.

 

Can I have two IF commands in one cell?

 

For example, I have the following in one cell:

 

=IF(E18 > C18,E18 - C18,0 - C18+E18)

 

which gives me the total hours worked, depending on what I write in the previous columns.

 

Elsewhere on the same sheet, I have a column for "codes".  If I type in W, the next column prints "Working", or OT prints "Overtime".  If I write "FH", it'll say "From Home" but the hours are still 0.

Can I change the above IF command to also look at the "Codes" column and if it says a specific code ("FH"), then it'll automatically change the hours to a number I set?

 

I know this is straying away from dumb timesheets but just wondering.

 

Thanks

[NoahGreat]

  On 03/10/2020 at 15:07, BudMan said:

Prob be easier to use a vlookup table.  You create a table with values and what you would like it to return.. Then in your z1 cell put in the formula to look up A1 in the table and return what you want to show in Z1.

 

This might help

https://exceljet.net/excel-functions/excel-vlookup-function/worktime

Expand  

Thank You for guide
But i recommend to add a HLOOKUP guide too for timesheets building