Calculating Hours Worked Past Midnight - Without no time entries equating to 24 hours.

[mttomkins]

Hi all,

Thanks in advance on this, my first post so please bear with me!

The point of this worksheet is equating additional hours worked and lieu time.

I'm having an issue with working out what a formula should be for the following:-

I have a worksheet with Date (A1) Time Started (B1) Time Finished (C1) Work Completed (D1) Hours Worked (E1) Holiday Used (F1)

With the B1 & B2 formatted as h:mm AM/PM and D2 as Number to 2 Decimal places. The Work Completed column either has free text or "Holiday" or "Day Off" both of which have IF formulas on the phrase updating another table in sheet 2.

Up to now I've been using =SUM(C1-B1)*24 in the Hours Worked column to work out the total amount of hours worked in a day however have just encountered the problem of working past midnight. As it returns a negative value instead of the total amount of hours.

I've then managed to solve this using the following IF formula in Hours Worked column =IF(C1>B1,SUM(C1-B1)*24,((C1-B1)+1)*24)

Again this works fine.

The ACTUAL issue now is on Days Off - as no time is inputted into B1 or C1 - which with the above formula results in 24 Hours being recorded in Hours Worked... when it needs to obviously be zero (0).

Can anyone think of a solution? In an ideal world i'd like to still be able to leave the time columns blank or have "Day Off" written into them which I'm hoping could then be incorporated into the IF formula above to equal a 0 in the Hours Worked (E1) column.

Couple of JPG's now attached to help give a bit of context. (I know original columns are incorrect but it was just to try and keep it simple)

Cheers!

[Special-K]

Attach a sample spreadsheet with expected results, not image files. Image files are impossible to edit and noone wants to retype the data for you.
Remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

[Richard Buttrey]

Hi, and welcome to the forum.

It would have helped if you had uploaded the workbook rather than just a picture. Pictures are no use when we need to test solutions since none of us want to spend time creating a workbook you already have.

However if I've understood the requirement the way to avoid negative time differences when times straddle midnight is to add 24 to the formula

e.g

Please Login or Register  to view this content.

and to avoid cells with no times showing 24, wrap the formula in another if test. e.g.

Please Login or Register  to view this content.

[mttomkins]

Thank you guys, apologies for attaching the image files rather than the doc. Have now updated.

The row with formula mentioned above is in Week 9 and highlighted.

[Richard Buttrey]

Hi,

Please Login or Register  to view this content.

[mttomkins]

Richard I've just tried implementing those myself...

The first one, solving the negative issue worked first time with the initial =SUM formula... meaning I didn't need the second part!

Cant believe it was that simple I've spent all day trying to work that out for the Mrs!

Thanks very much