Thread: excel help

thought i could throw a few formulas into our corporate timesheet that would sort normal hours from the two overtime brackets to make it easier for the boys

the guys enter there start and finish times along with a 0:30min (.5hr) unpaid meal break

start 06:00
Meal 00:30 (unpaid)
finish 14:30
Total hrs worked 8hr
Standard work day 8hr
O/T< 3hr = 0
O/T > 3hr = 0


Above is there standard 8hr day

Start 05:30
meal 00:30 minus
Finish 15:30
Total Hrs worked 12hrs
Standard work day 8hr
O/T < 3hr = 3:00
O/T > 3hr = 1:00

so for a 12 hour day how do i get the O/T <3hr and O/T > 3hrs to automatically drop in, keep in mind the < 3hr cannot display no more than 3hr

as this is a corporate form i cannot change the layout, infact they are not keen on adding formulas however as we need to print them off they will never know

the other wierd thing is we start in 24hr format and finish off in decimal i.e i cannot work out how to add the overtime up if its kept on hrs

grrr

does that make sense?

edit, seems i cannot upload a xlsm file?

Originally Posted by ian_gregg

Time sheet with formulas.xlsx

Try this. One trick is to know that times are actually numbers less than 1 as fractions of a day so multiply by 24 to get hours. Other than that it is just mixing up if() functions.

Ian,

Thanks for this - I was struggling with the time format myself, the *24 has solved that one for me.

Cheers,

Franz