Time calculation help please

[Jay2000]

I am trying to compute a formula which will allow me to figure out the hours worked for a person. It worked very well until I ran into third shift people who would work beyond 12:00am.

I then found the following formula:

=TEXT(IF(B1<A1,1-A1+B1,B1-A1),"hh:mm")

However this returns the time they worked and not a number.

For example:

Time In 5:30 PM
Time Out 2:00 AM
Total Time worked 8:30 is what is returned
What I want is 8.5

Unfortunately, I think I am making this harder than it is and I am drawing a blank.

Thank you

Jay

 

[Brian from Maui]

I am trying to compute a formula which will allow me to figure out the hours worked for a person. It worked very well until I ran into third shift people who would work beyond 12:00am.

I then found the following formula:

=TEXT(IF(B1<A1,1-A1+B1,B1-A1),"hh:mm")

However this returns the time they worked and not a number.

For example:

Time In 5:30 PM
Time Out 2:00 AM
Total Time worked 8:30 is what is returned
What I want is 8.5

Unfortunately, I think I am making this harder than it is and I am drawing a blank.

Thank you

Jay

Why are you returning text, by using the TEXT function? Multiply by 24 to return a decimal number.

 

[Jay2000]

Hmm...

For some reason my formula did not post.

Hopefully it will post this time:

=TEXT(IF(B1<A1,1-A1+B1,B1-A1),"hh:mm")

 

[NBVC]

Just multiply your function by 24 and format your cell as General or Number.

 

[NBVC]

Re: Hmm...

For some reason my formula did not post.

Hopefully it will post this time:

=TEXT(IF(B1<A1,1-A1+B1,B1-A1),"hh:mm")

=TEXT(IF(B1<A1,1-A1+B1,B1-A1)

Is that your formula?

Use the Code button before and after the code you want to enter.

 

[fairwinds]

=TEXT(IF(B1 < A1,1-A1+B1,B1-A1),"hh:mm")

Try:

=(B1-A1+(B1 < A1))*24