Calculating Hours Worked and breaks taken

Mrtreasurer

New Member
Joined
Sep 7, 2019
Messages
12
Hello

I thought I already posted this question but couldn’t find it anyways here I go

I am working on a weekly staffing schedule and it has the follow info type

C13 = start time ( 8:30 Am)
D13=End-time (5:00 PM)
E13 =Hours worked ( subtracting breaks with the following rules )

Works < 6 hours ( no break)
Works >8 hours (30 minute break)

Thanks
Ed
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Mrtreasurer

New Member
Joined
Sep 7, 2019
Messages
12
If they work less than 6 no break and if they work >7 30 minute break .. I was going to use >8 hours but it makes more sense to use 7
 

Mrtreasurer

New Member
Joined
Sep 7, 2019
Messages
12
Anything over 6 hours gets a 30 minute break .. eg 6 Hours 1 minutes
 

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
A possibility could be .. but I know there is more simple one
=IF((D13-C13)*24*60> 6*60,(D13-C13)*24*60-30,(D13-C13)*24*60)/60/24
 

Mrtreasurer

New Member
Joined
Sep 7, 2019
Messages
12
Thanks I’ll try that.. I hope it works thanks so much for taking the time
 

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
Could not get this formula to work

What's the result or message
Here what I got

C13 = 08:30 AM
D13 = 5:30 PM
FA3 = 08:30 AM
 

Mrtreasurer

New Member
Joined
Sep 7, 2019
Messages
12
I am not sure what you are referencing (FA3 = 08:30 AM what is this??) but when I apply the formula in cell E13 I am returning with 0.35416667

My spreadsheet is laid out as follows: C13 = 08:30 AM
D13 = 5:30 PM
F13 = ( 0.35416667) should look like (Hours worked - break )
or 9 hours worked - 0:30minutes = 8hours 30 minutes
 

Watch MrExcel Video

Forum statistics

Threads
1,089,762
Messages
5,410,276
Members
403,306
Latest member
ekastan

This Week's Hot Topics

Top