1. ## Hours worked issue

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

2. 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,

3. Hi jPaulB

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

=MOD(B2-B1,1)

4. 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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•