Hours worked issue

jPaulB

New member
Joined
Sep 26, 2013
Messages
7
Reaction score
0
Points
1
Location
Moncton, NB, Canada
Excel Version(s)
Microsoft Office 2019
Hi Everybody,


I have a job that starts at 4:00PM and runs until about 7:30am the following morning.

I've managed to set the Start Time in say cell B1 and format it for time. It will indicate 4:00PM and the numeric value is: 0.6667

The End Time is in cell B2 and also formatted for time. That will indicate 0.3125

I ought to be able to take and (End Time) - (Start Time) and get a number, -0.3254

The question I have is "How do I express that result -0.3254 in terms of hours?", and "How would I remove a 30 minute break from that result and still display in terms of hours?"

Many thanks,

Paul
 

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
Hello,

I put the Start Time in B2, End Time in B3, and "Minutes of Breaks" in B4. Use the formula below:

=(IF(B2>B3,1+(B3-B2),B3-B2)-(B4/60/24))*24

Because of the way the times work, if you work overnight, you'll have to take 1 + your number otherwise you will be shorted. Time is technically Month/Day/Year Hours:Minutes:Seconds in excel. It gave you a negative number because it assumes you worked on 1/0/1900. So you actually worked back in time :).

Hope this helps,
 

Kevin@Radstock

New member
Joined
Oct 5, 2012
Messages
52
Reaction score
0
Points
0
Excel Version(s)
365
Hi jPaulB

Try the following formula and format the cell as [h]:mm, this will return the value in time.

=MOD(B2-B1,1)
 

jPaulB

New member
Joined
Sep 26, 2013
Messages
7
Reaction score
0
Points
1
Location
Moncton, NB, Canada
Excel Version(s)
Microsoft Office 2019
Thank you so much, bgoree09 and Kevin!!

I had spent so much time messing around with "concepts" and "weird" that I convinced myself that it had to be complicated. I feel like Old Mr. Mazerolle (grade 4 math) is gonna smack the back of mt head.

Thank you fellows,
Paul
 
Top