Difference in hours between dates.

  • Thread starter Thread starter robert_woodie
  • Start date Start date
R

robert_woodie

I have a start and finish time of a job in the format:
dd/mm/yyyy hh:mm
I need a formula which will calculate the number of hours this job ha
taken, so i need it to exclude weekends and bank holidays(which
believe can be done using network days function) it also needs to tak
into account that 1 working day is 7.5 hours.

Iam sure this question has been asked before so any formulas or link
would be much appreciated.

Thanks
Robert
 
Hi Robert!

I've made some assumptions but try:

=(NETWORKDAYS(A1,B1,H1:H10)-1)*7.5+MIN(TIME(17,30,0)-TIME(HOUR(A1),MIN
UTE(A1),SECOND(A1)),"07:30:00")*24-(TIME(HOUR(A1),MINUTE(A1),SECOND(A1
))<=TIME(13,0,0))+MIN(TIME(HOUR(B1),MINUTE(B1),SECOND(B1))-"09:00:00",
"07:30:00")*24-(TIME(HOUR(B1),MINUTE(B1),SECOND(B1))>=TIME(12,0,0))

I've used NETWORKDAYS to get the number of days and then deducted 1 so
that neither the starting nor ending day is counted. This is
multiplied by 7.5. Holidays are in the range H1:H10

With the days at each end, I've assumed a starting time of 9:00 AM and
a finish time of 17:30 PM with a hour for lunch between 12:00 and
13:00.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
This is the kinda thing iam after, but i think it is 5.5 hours out. I
cant see where though. Can anybody help?

Thanks again
Robert.
 
Hi Robert!

Give the example of the error and I'll try and attack it in the
morning.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
A B C
1 | 02/01/2004 09:00 02/01/2004 17:30 13

C1 should equal 7.5 hours (1 working day)

Formula in C1:
=(NETWORKDAYS(A1,B1,H1:H10)-1)*7.5+MIN(TIME(17,30,0)-TIME(HOUR(A1),MINUTE(A1),SECOND(A1)),"07:30:00")*24-(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))<=TIME(13,0,0))+MIN(TIME(HOUR(B1),MINUTE(B1),SECOND(B1))-"09:00:00","07:30:00")*24-(TIME(HOUR(B1),MINUTE(B1),SECOND(B1))>=TIME(12,0,0))

Thanks
Rober
 
Hi Robert!

I hadn't anticipated the same day! Try:

=(NETWORKDAYS(A1,B1,H1:H10)-1)*7.5+MIN(TIME(17,30,0)-TIME(HOUR(A1),MIN
UTE(A1),SECOND(A1)),"07:30:00")*24-(TIME(HOUR(A1),MINUTE(A1),SECOND(A1
))<=TIME(13,0,0))+(DATE(YEAR(A1),MONTH(A1),DAY(A1))<>DATE(YEAR(B1),MON
TH(B1),DAY(B1))*MIN(TIME(HOUR(B1),MINUTE(B1),SECOND(B1))-"09:00:00","0
7:30:00")*24-(TIME(HOUR(B1),MINUTE(B1),SECOND(B1))>=TIME(12,0,0)))

The calculation for the second day is multiplied by:
(DATE(YEAR(A1),MONTH(A1),DAY(A1))<>DATE(YEAR(B1),MONTH(B1),DAY(B1)))

This returns FALSE if it's the same day and TRUE if not. These get
coerced to 0 and 1 by the maths operation.

Not fully tested as my bed is looking very comfortable.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top