Snasui Excel Forum

Hi Dear
I need your help how to calculate difference between two dates and time (21:00-7:00)
The time between 21:00-07:00 is the night working time. How can the C column formula be in this case?
I attached a sample

Thanks

Still got no answer yet

Try these formulas,

1. D1:H1 fill Start, End, Part1, Part2 and Total respectively
2. D2
   =MOD(A2,1)
   Enter > Copy down
3. E2
   =MOD(B2,1)
   Enter > Copy down
4. F2
   =IF(D2=0,IF(E2<=7/24,E2),MAX(0,7/24-D2))
   Enter > Copy down
5. G2
   =IF(D2>=21/24,MAX(0,E2-D2+(E2<D2)),IF(AND(D2<=21/24,E2>=21/24),E2-21/24))+INT(B2-A2)*10/24
   Enter > Copy down
6. H2
   =F2+G2
   Enter > Copy down

Thank you so much

D2
=((DAYS(B2-21/24,A2-21/24)+1)*10-MIN(10,MOD(A2*24-21,24))-MAX(0,10-MOD(B2*24-21,24)))/24

Thank Bo_ry for a nice solution as well