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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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,090,298
Messages
5,413,630
Members
403,496
Latest member
chamshop

This Week's Hot Topics

Top