Link to home
Start Free TrialLog in
Avatar of Koen
KoenFlag for Belgium

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
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Koen

ASKER

rgonzo, works for your dates, but I put in these:

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...)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rgonzo1971
Rgonzo1971

in col J try this formula
=MAX(0,(I2-H2)+(G2*TIME(1,,)))

Open in new window

See attached.

Convert Start Date-Time:
=IF(WEEKDAY(A3,2)>5,CEILING(INT(A3),7)+2+TIME(8,0,0),INT(A3)+MAX(MOD(A3,1),TIME(8,0,0)))

Convert Finish Date-Time:
=IF(WEEKDAY(B3,2)>5,FLOOR(INT(B3),7)-1+TIME(18,0,0),INT(B3)+MIN(MOD(B3,1),TIME(18,0,0)))

Duration:
=MAX(0,NETWORKDAYS(INT(D3)+1,INT(E3),Holidays)*10+(MOD(D3,1)*24)+(MOD(E3,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
Hold fire on the duration calculation, not quite right on the partial days part.
Avatar of Koen

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
Still working on some amendments for different scenarios.

So far:

Convert Start Date-Time:
=IF(OR(WEEKDAY(A12,2)>5,AND(WEEKDAY(A12,2)=5,MOD(A12,1)>TIME(18,0,0))),CEILING(INT(A12),7)+2+TIME(8,0,0),INT(A12)+MAX(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(B9,2)=1,MOD(B9,1)<TIME(8,0,0))),FLOOR(INT(B9),7)-1+TIME(18,0,0),INT(B9)+MIN(MOD(B9,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!!
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.
Think I now have the Duration correct:

=MAX(0,NETWORKDAYS(INT(D12)+1,INT(E12)-IF(MOD(E12,1)<TIME(18,0,0),1,0),Holidays))*10+((TIME(18,0,0)-MOD(D12,1))+(TIME(18,0,0)-MOD(E12,1)))*24
Avatar of Koen

ASKER

Rgonzo, you were on the right track, the addition of Shaun completed it?

tx