Time calculation help please

Jay2000

New Member
Joined
Dec 5, 2005
Messages
6
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
Jay2000 said:
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

New Member
Joined
Dec 5, 2005
Messages
6
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Just multiply your function by 24 and format your cell as General or Number.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Re: Hmm...

Jay2000 said:
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")

Code:
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,727
Messages
5,573,861
Members
412,555
Latest member
mark84
Top