Formula to Calculate Specific Span of Time

Big Bird

New Member
Joined
Mar 25, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
I am trying to create a formula to calculate the number of hours worked between 12a-6a (overtime pay) and number of hours worked between 6a-12a (regular pay) so avoid making calculations in our heads and possibly making mistakes. I'm using Excel 2013 with Windows.
ABCDEFGHI
1Name of EmployeeClock In DateClock In TimeClock Out DateClock Out TimeRegular HoursOvertime HoursTotal Hours
2John1/1/2012:00 AM1/1/206:00 AM066
3Jane1/1/206:00 AM1/1/202:00 PM808
4Bob1/1/2011:00 PM1/2/203:00 AM134
5Betty1/1/205:00 AM1/1/209:00 AM314

I've tried using various IF/AND formulas and have most recently been attempting with fractions of time but I haven't been able to put together a single formula that works for the four scenarios above. I think I'll need two different but similar formulas - one to calculate Regular Hours and one to calculate Overtime Hours. This was my most recent attempt at Regular Hours which works for rows 2 & 3 in the above table but not for rows 4 & 5:
=IF(AND(C2>=0,E2<=6/24),0,IF(AND(C2>=6/24,E2<=1),(E2-C2)*24,IF(AND(C2<1,E2>=0),24-(C2*24),IF(AND(C2<6/24,E2>=6/24),(E2-6/24)*24))))

My brain is fried, I've been working on this for so long. Any help will be much appreciated.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,958
Office Version
  1. 365
Platform
  1. Windows
I haven't tested this beyond the examples provided,
Book123.xlsm
ABCDEFGH
1Name of EmployeeClock In DateClock In TimeClock Out DateClock Out TimeRegular HoursOvertime HoursTotal Hours
2John01/01/202012:00 AM01/01/20206:00 AM066
3Jane01/01/20206:00 AM01/01/20202:00 PM808
4Bob01/01/202011:00 PM02/01/20203:00 AM134
5Betty01/01/20205:00 AM01/01/20209:00 AM314
Sheet9
Cell Formulas
RangeFormula
F2:F5F2=H2-G2
G2:G5G2=MOD(MEDIAN(D2+"00:00",D2+"06:00",D2+E2)-MEDIAN(D2+"00:00",D2+"06:00",B2+C2),1)*24
H2:H5H2=((D2+E2)-(B2+C2))*24
 

Big Bird

New Member
Joined
Mar 25, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
jasonb75 - thank you so, so much! Your formulas do exactly what I want them to do!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,829
Messages
5,598,352
Members
414,233
Latest member
WolverineNurse

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top