# Math with Time

#### nikneven

##### Board Regular
ok, so in my spreed sheet i need cell to do some time calculations. Basically it is payroll for employees. They get paid overtime for everything between midnight and 6am. its set up like this...

A1 is the Start Time
A2 is the Duration
T1 needs to be number of hours worked between 6am and 11:59 pm
T2 needs to be number of hours worked between 12:00am and 5:59 pm

so, if

A1 =4 PM
A2= 10
then i need a formula to make T1 = 8 and T2 = 2

and the kick, it needs to span days, so if
A1=10pm
A2=10
T1=4
T2=6.
Get it?
help! Excels date and time stuff boggles my mind...

thanks
nik

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

#### barry houdini

##### MrExcel MVP
Hi nik,

assuming A1 contains a valid time, e.g. 10 pm or 22:00 etc. and A2 is 18 or less then try this formula in T2

=IF(A1+A2/24<1,MAX(0,MIN(6,A1*24+A2)-A1*24),MIN(6,MOD(A1*24+A2,24)))

and this in T1

=A2-T2

#### nikneven

##### Board Regular
will that not work for anything over 18? sometimes it goes up to twenty.

#### barry houdini

##### MrExcel MVP
I was trying to avoid overcomplicating the formula but to accommodate up to 20 hours change formula in T2 to this

=IF(A1+A2/24<1,MAX(0,MIN(6,A1*24+A2)-A1*24),MIN(6,MOD(A1*24+A2,24))+MAX(0,6-A1*24))

Replies
3
Views
709
Replies
2
Views
282
Replies
0
Views
546
Replies
0
Views
157
Replies
3
Views
326

1,171,967
Messages
5,878,534
Members
433,348
Latest member
eramirez148

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

### Which adblocker are you using?

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

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