24hour timesheet formula query??

[vollis]

Hi

I have a timesheet which is working well apart from in one particular scenario.

Basically I have created the timesheet in 24 hr format, there is a 'start time' column, 'finish time' column and ' break time taken' column - which then calculate the number of hours worked.

So this works when the working day does not pass over midnight so the following example calculates with no problems: =IF(D36>0,E36-D36-F36,0)
COL D - start time is 18:00
COL E - finish is 23:59
COL F - break is 00:20

However if I input the following
COL D - start time 18:00
COL E - end time 00:01
COL F - break 00:20

the formula does not calculate???

I have played around with this and what I think I have tried to say is : =IF(E37>0<D37,24-D37-F37,IF(D37>0,E37-D37-F37,0))

I know this is wrong but I dont how to translate into a workable formula??? can you help???

[Spencer101]

Hi, could you post an dummy version? Makes it far easier to help out in such matters.

[benishiryo]

hi vollis, welcome to the forum. do you have a Start Date & End Date column? you should add that to your time. Let's say Start Date is at H36 and EndDate is at I36. you should have a column to add H36+ D36 and another column to add I36 + E36. you can then use your formula to refer to these 2 columns instead

[vollis]

TIMESHEET FORMULA QUERY.jpg

Hi I have attached an image of the sheet - couldnt see how to attach an actual file ( sorry, new to the forum so acting like a noob!)

Hope you can access the attachment ok?

[Spencer101]

Would this formula in row 37 for example do what you want?

Obviously change all instances of 37 for the relevant row number in your worksheet.

=IF(E37<D37,(E37+24)-D37-F37,E37-D37-F37)

[vollis]

Hi Spencer101

I have tried this and I think it is almost there - as you can see from the attachment there is no longer an error but the value of hours worked is returned as "0" ??

Could it be the '24' in the formula throwing out the 24 hour format ???? I am not great at dealing with 24 hr formulas in excel once they surpass a basic level! -

Any Ideas??TIMESHEET FORMULA QUERY1.jpg

[Spencer101]

Have a look at the attachment. Works for me...

Might need to check the formatting of your cell to ensure it's set to HH:MM

[vollis]

Brilliant !!!!

Thanks you sooo much for your help - I now have a working sheet.....

[Spencer101]

Not a problem. Glad to be of help.

S.