Koen
asked on
excel time calculation
I've been fooling around with time calculation, but can't seem to fully graps the desired result...
What I want:
I want the real business hours that have passed between two fields (date and time), based on 10hour working day (8am - 6pm), 5 days a week.
column a : start date (date + hh:mm)
column b : end date (date + hh:mm)
so any column a that starts after hours (or weekend) should start at 8 am on the next workingday
any column b that ends after hours (or weekend) should end at 6 pm on the last workingday
result in hours (fractional).
it's probably not that hard, but i've messed so much with networkingdays, datedif, etc... that I've lost the vision...
Tx
What I want:
I want the real business hours that have passed between two fields (date and time), based on 10hour working day (8am - 6pm), 5 days a week.
column a : start date (date + hh:mm)
column b : end date (date + hh:mm)
so any column a that starts after hours (or weekend) should start at 8 am on the next workingday
any column b that ends after hours (or weekend) should end at 6 pm on the last workingday
result in hours (fractional).
it's probably not that hard, but i've messed so much with networkingdays, datedif, etc... that I've lost the vision...
Tx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
in col J try this formula
=MAX(0,(I2-H2)+(G2*TIME(1,,)))
See attached.
Convert Start Date-Time:
=IF(WEEKDAY(A3,2)>5,CEILIN G(INT(A3), 7)+2+TIME( 8,0,0),INT (A3)+MAX(M OD(A3,1),T IME(8,0,0) ))
Convert Finish Date-Time:
=IF(WEEKDAY(B3,2)>5,FLOOR( INT(B3),7) -1+TIME(18 ,0,0),INT( B3)+MIN(MO D(B3,1),TI ME(18,0,0) ))
Duration:
=MAX(0,NETWORKDAYS(INT(D3) +1,INT(E3) ,Holidays)*10+(MOD(D3,1)*24)+(MOD(E 3,1)*24))
In the attached workbook I have not included a Holiday range but would be included in the formula as indicated above in italics.
Time-calcs.xlsx
Convert Start Date-Time:
=IF(WEEKDAY(A3,2)>5,CEILIN
Convert Finish Date-Time:
=IF(WEEKDAY(B3,2)>5,FLOOR(
Duration:
=MAX(0,NETWORKDAYS(INT(D3)
In the attached workbook I have not included a Holiday range but would be included in the formula as indicated above in italics.
Time-calcs.xlsx
Hold fire on the duration calculation, not quite right on the partial days part.
ASKER
Rob, i'm sorry, but yours does not seem to give the desired result...
i changed start hour to 12:00 and end hour to 13:00 (to simplify counting) and then the first line should be 76 hours, you have 90
the last one (again 12 and 13h) should be 45 you have 61
i changed start hour to 12:00 and end hour to 13:00 (to simplify counting) and then the first line should be 76 hours, you have 90
the last one (again 12 and 13h) should be 45 you have 61
Still working on some amendments for different scenarios.
So far:
Convert Start Date-Time:
=IF(OR(WEEKDAY(A12,2)>5,AN D(WEEKDAY( A12,2)=5,M OD(A12,1)> TIME(18,0, 0))),CEILI NG(INT(A12 ),7)+2+TIM E(8,0,0),I NT(A12)+MA X(MOD(A12, 1),TIME(8, 0,0)))
Allows for start date being a working day but start time outside of work hours, ie early Monday (rounds up time to 8:00) or late Friday (rounds up to 8:00 on following Monday).
Convert Finish Date-Time:
=IF(OR(WEEKDAY(B9,2)>5,AND (WEEKDAY(B 9,2)=1,MOD (B9,1)<TIM E(8,0,0))) ,FLOOR(INT (B9),7)-1+ TIME(18,0, 0),INT(B9) +MIN(MOD(B 9,1),TIME( 18,0,0)))
Allows for finish date being a working day but finish time outside of work hours, ie early Monday (rounds back time to 18:00 previous Friday) or late Friday (rounds back to 18:00 that day).
Duration:
Still working!!
So far:
Convert Start Date-Time:
=IF(OR(WEEKDAY(A12,2)>5,AN
Allows for start date being a working day but start time outside of work hours, ie early Monday (rounds up time to 8:00) or late Friday (rounds up to 8:00 on following Monday).
Convert Finish Date-Time:
=IF(OR(WEEKDAY(B9,2)>5,AND
Allows for finish date being a working day but finish time outside of work hours, ie early Monday (rounds back time to 18:00 previous Friday) or late Friday (rounds back to 18:00 that day).
Duration:
Still working!!
Not sure I agree on your 76 hours for the first line
06/10/2016 12:00 to 15/10/2016 13:00
Whole days = 7th, 10th, 11th, 12th, 13th, 14th = 6 days = 60 hours
Part days = 12:00 to 18:00 of 6th = 6 hours
Total = 66 hours
08/10/2016 12:00 to 14/10/2016 13:00
Whole days = 10th, 11th, 12th, 13th = 4 days = 40 hours
Part days = 8:00 to 13:00 of 14th = 5 hours
Total = 45
I agree on the second one and this is using the same logic.
06/10/2016 12:00 to 15/10/2016 13:00
Whole days = 7th, 10th, 11th, 12th, 13th, 14th = 6 days = 60 hours
Part days = 12:00 to 18:00 of 6th = 6 hours
Total = 66 hours
08/10/2016 12:00 to 14/10/2016 13:00
Whole days = 10th, 11th, 12th, 13th = 4 days = 40 hours
Part days = 8:00 to 13:00 of 14th = 5 hours
Total = 45
I agree on the second one and this is using the same logic.
Think I now have the Duration correct:
=MAX(0,NETWORKDAYS(INT(D12 )+1,INT(E1 2)-IF(MOD( E12,1)<TIM E(18,0,0), 1,0),Holidays))*10+((TIME(18,0,0)-MOD(D 12,1))+(TI ME(18,0,0) -MOD(E12,1 )))*24
=MAX(0,NETWORKDAYS(INT(D12
ASKER
Rgonzo, you were on the right track, the addition of Shaun completed it?
tx
tx
ASKER
8/10/2016 15:20 9/10/2016 3:13, gives value error (probably because it falls completely in after hours)
(it's a bit of a hassle... start and/or end can fall within or outside of business hours, but reporting only counts business hours...)