Formulas involving time...

[Finalfrontier1976]

Hi All,

I'm trying to construct a spreadsheet but have hit a stumbling block (spreadsheet attached).

What I'm trying to do is log drivers details and the hours they have worked. I want to enter the start time and the end time and get the sheet to do the rest.

However, if let's say the driver started at 14:00 and ended his shift on 03:00 then the simple formula =sum(cell containing 03:00-cell containting 14:00) brings a negative number which is giving ######### in the cell. So I've tried this formula:

=IF(C3>D3,C3-D3,"-"&TEXT(ABS(C3>D3),"[h]:mm"))

Which seems to work.

However, with using this formula with values that would not usually give a negative number I'm getting the result of -0:00.

Can anyone help before I chuck this PC out the window?

Thanks
Lewis

[arthurbr]

try =IF(A1>B1,B1+1-A1,B1-A1) ( adapt cell ref as needed)

[Finalfrontier1976]

Thanks,

But, that formula works ok with positive results but not negative times where the finish time (in 24hr clock is less than the start time).

[arthurbr]

Works OK for me where A1 is start and B1 is end

[arthurbr]

Sheet added ( hope it's OK worked with OpenOffice) - Maybe ; should be changed to ,

[Finalfrontier1976]

Great,

It works now, must have been me.

Thank you.

[arthurbr]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[Finalfrontier1976]

Cont...

One last question, further to this I've been asked then to take the time worked and minus 8hrs from it to give me the overtime amount of hours.

That I can do no problem as the basic rate is in another colum so it's just a case of say:

In cell A3 overtime worked would be:

=sum(a1 (total hours worked) - a2 (8hrs basic pay) )

What I would like to do then from this overtime amount worked is minus 45mins which is their break period. I've tired a few things but no joy, for example:

=sum(a3-00:45) but I'm now getting erros.

[NBVC]

Try

=A3-Time(0,45,0)

or A3-0.75