NETWORKDAYS but counting the hours not the days...

[Guest]

PLEASE PLEASE HELP,

What I need are the excat hours/minutes between 2 dates, my date format is as follows: -

a) 24/06/04 12:15:11
b) 25/06/04 13:10:03

The hours I want counted for in a day are between 09:00 & 17:00. My formula for counting the hours/mintues are fine as long as the dates are on the same day, my problem is it brings me back an "#NUM!" error when the dates are different. Can anyone help me here?? Please find below my current formula: -

=IF(AND(INT(L2)=INT(O2),NOT(ISNA(MATCH(INT(L2),U:U,0)))),0,ABS(IF(INT(L2)=INT(O2),ROUND(24*(O2-L2),2),(24*($V$5-$V$4)*(MAX(NETWORKDAYS(L2+1,O2-1,U:U),0)+INT(24*(((O2-INT(O2))-(L2-INT(L2)))+($V$5-$V$4))/(24*($V$5-$V$4))))+MOD(ROUND(((24*(O2-INT(O2)))-24*$V$4)+(24*$V$5-(24*(L2-INT(L2)))),2),ROUND((24*($V$5-$V$4)),2))))))

- L2 = START DATE
- O2 = COMPLETED DATE
- V4 = 09:00
- V5 = 17:00

Thanks again.
James.

 

[Ron Rosenfeld]

PLEASE PLEASE HELP,

What I need are the excat hours/minutes between 2 dates, my date format is as follows: -

a) 24/06/04 12:15:11
b) 25/06/04 13:10:03

From the Subject line vs your description, I'm not sure if you want to use
NETWORKDAYS or not. So depending, what about:

=TIME(17,0,0)-MOD(A1,1)+(NETWORKDAYS(A1,A2)-2)*8/24+MOD(A2,1)-TIME(9,0,0)

or

=TIME(17,0,0)-MOD(A1,1)+MOD(A2,1)-TIME(9,0,0)+(INT(A2)-INT(A1)-1)*8/24

Custom format the cell as [h]:mm

This assumes that the times entered are also between 9:00 and 17:00; the
earlier date/time in A1; the later date/time in A2; and that the date/times are
understood as such by XL (i.e. not text).

I have not extensively tested this.




--ron

 

[Guest]

PLEASE PLEASE HELP,

What I need are the excat hours/minutes between 2 dates, my date format is as follows: -

a) 24/06/04 12:15:11
b) 25/06/04 13:10:03

From the Subject line vs your description, I'm not sure if you want to use
NETWORKDAYS or not. So depending, what about:

=TIME(17,0,0)-MOD(A1,1)+(NETWORKDAYS(A1,A2)-2)*8/24+MOD(A2,1)-TIME(9,0,0)

or

=TIME(17,0,0)-MOD(A1,1)+MOD(A2,1)-TIME(9,0,0)+(INT(A2)-INT(A1)-1)*8/24

Custom format the cell as [h]:mm

This assumes that the times entered are also between 9:00 and 17:00; the
earlier date/time in A1; the later date/time in A2; and that the date/times are
understood as such by XL (i.e. not text).

I have not extensively tested this.




--ron

Thanks Ron,

I do require the networkdays function, as weekends are not required for project. Your formula works fine but seems to draw the following problems: -

20/06/2004 20:55:18 (Start Date & time)
21/06/2004 16:08:15 (End Date & time)

The above dates bring back an error and the problem being is that I've many of these dates in my data. Using the 09:00-17:00 working day the result should be 07:08:15 but instead I get an error. Another problem as follows: -

20/06/2004 20:55:31 (Start Date & time)
22/06/2004 16:23:53 (End Date & time)

The result of the above brings back 03:28 when really it should be 15:23:53 (08:00:00 from 21/06/2004 and 07:23:53 from 22/06/2004).

Any help on this matter would be much much appreciated, this thing is killing me!!

thanks again.
James.

 

[Guest]

PLEASE PLEASE HELP,

What I need are the excat hours/minutes between 2 dates, my date format is as follows: -

a) 24/06/04 12:15:11
b) 25/06/04 13:10:03

From the Subject line vs your description, I'm not sure if you want to use
NETWORKDAYS or not. So depending, what about:

=TIME(17,0,0)-MOD(A1,1)+(NETWORKDAYS(A1,A2)-2)*8/24+MOD(A2,1)-TIME(9,0,0)

or

=TIME(17,0,0)-MOD(A1,1)+MOD(A2,1)-TIME(9,0,0)+(INT(A2)-INT(A1)-1)*8/24

Custom format the cell as [h]:mm

This assumes that the times entered are also between 9:00 and 17:00; the
earlier date/time in A1; the later date/time in A2; and that the date/times are
understood as such by XL (i.e. not text).

I have not extensively tested this.




--ron

Thanks Ron,

I do require the networkdays function, as weekends are not required for project. Your formula works fine but seems to draw the following problems: -

20/06/2004 20:55:18 (Start Date & time)
21/06/2004 16:08:15 (End Date & time)

The above dates bring back an error and the problem being is that I've many of these dates in my data. Using the 09:00-17:00 working day the result should be 07:08:15 but instead I get an error. Another problem as follows: -

20/06/2004 20:55:31 (Start Date & time)
22/06/2004 16:23:53 (End Date & time)

The result of the above brings back 03:28 when really it should be 15:23:53 (08:00:00 from 21/06/2004 and 07:23:53 from 22/06/2004).

Any help on this matter would be much much appreciated, this thing is killing me!!

thanks again.
James.

 

[Ron Rosenfeld]

I do require the networkdays function, as weekends are not required for project. Your formula works fine but seems to draw the following problems: -

20/06/2004 20:55:18 (Start Date & time)
21/06/2004 16:08:15 (End Date & time)

The above dates bring back an error and the problem being is that I've many of these dates in my data. Using the 09:00-17:00 working day the result should be 07:08:15 but instead I get an error. Another problem as follows: -

20/06/2004 20:55:31 (Start Date & time)
22/06/2004 16:23:53 (End Date & time)

The result of the above brings back 03:28 when really it should be 15:23:53 (08:00:00 from 21/06/2004 and 07:23:53 from 22/06/2004).

Any help on this matter would be much much appreciated, this thing is killing me!!

OK, in my first posting, based on your examples, I assumed the beginning and
ending times would be on a workday/time (i.e. m-f between 9am and 5pm).

But since these times can be random, try this formula:

=MAX(0,TIME(17,0,0)-MAX(MOD(A1,1),TIME(9,0,0)))+
(NETWORKDAYS(INT(A1)+1,INT(A2)-1))*8/24+
MAX(0,MIN(MOD(A2,1),TIME(17,0,0))-TIME(9,0,0))

Again, I have not extensively tested this.


--ron

 

[Guest]

Thanks again Ron,

This current formula doesn't even work, just brings me back "######". Bascially we have a team that works 9-5 Mon-Fri and NOT weekends, now this team have issues with tasks coming to them after core business hours with which their accounted for. So I need a formula to calculate their accountable hours across their core business hours 9-5 Mon-Fri and NOT weekends. Again any help will be most appreicated.

Thanks again.
James.

 

[Daniel.M]

Hi,

=MAX($V$4,MIN($V$5,MOD(O2,1)))-MAX($V$4,MIN($V$5,MOD(L2,1)))-
($V$4-$V$5)*(INT(O2)-INT(L2))

Format as [h]:mm:ss

Regards,

Daniel M.

PLEASE PLEASE HELP,

What I need are the excat hours/minutes between 2 dates, my date format is as follows: -

a) 24/06/04 12:15:11
b) 25/06/04 13:10:03

The hours I want counted for in a day are between 09:00 & 17:00. My formula

for counting the hours/mintues are fine as long as the dates are on the same
day, my problem is it brings me back an "#NUM!" error when the dates are
different. Can anyone help me here?? Please find below my current formula: -

 

[Ron Rosenfeld]

Thanks again Ron,

This current formula doesn't even work, just brings me back "######". Bascially we have a team that works 9-5 Mon-Fri and NOT weekends, now this team have issues with tasks coming to them after core business hours with which their accounted for. So I need a formula to calculate their accountable hours across their core business hours 9-5 Mon-Fri and NOT weekends. Again any help will be most appreicated.

I overlooked something in the last formula. Try this:

=MAX(0,TIME(17,0,0)-MAX(MOD(A1,1),TIME(9,0,0)))+
MAX(0,(NETWORKDAYS(INT(A1)+1,INT(A2)-1)))*8/24+
MAX(0,MIN(MOD(A2,1),TIME(17,0,0))-TIME(9,0,0))


--ron

 

[Daniel.M]

=MAX($V$4,MIN($V$5,MOD(O2,1)))-MAX($V$4,MIN($V$5,MOD(L2,1)))-

($V$4-$V$5)*(INT(O2)-INT(L2))

Format as [h]:mm:ss

And if you don't want to count hours during the Week-End:

=IF(L2>O2,0,NETWORKDAYS(L2,O2)*($V$5-$V$4)
-IF(NETWORKDAYS(L2,L2),MAX(0,MIN($V$5,MOD(L2,1))-$V$4),0)
-IF(NETWORKDAYS(O2,O2),MAX(0,$V$5-MAX(MOD(O2,1),$V$4)),0))

Regards,

Daniel M.

 

[Myrna Larson]

Hi, James:

This problem is sufficiently complicated that I would do it in VBA (even thous
it will be slower).

Here's a VBA function that should do what you need. For the examples you gave,
it produces the same results.

To use this, open your workbook, then go to the VB Editor (ALT+F11). Select
your workbook from the list in the upper left pane. Then go to the Insert menu
and click on Module.

Paste the code below (all lines between the tildes) into the blank pane you
now see on the right.

To call the function from the worksheet, use a formula like this:

=WorkedHours(B1,B2,$K$1:$K$10)

In the above, the starting date and time are in B1, the ending date and time
in B2, and a list of holidays in the range K1:K10.

As with the NetWorkdays function, the holidays are optional. If you don't
want/need to exclude holidays, you can write the formula as

=WorkedHours(B1,B2)

Note the workday is hard-coded as 9:00 to 17:00.

Be sure to format the cell containing the formula with the Custom format that
Ron gave you, i.e. [h]:mm

If you have problems, post back.

'~~~~~~~~~~~~~~~~~~~
Option Explicit

Function WorkedHours(Date1 As Double, Date2 As Double, _
Optional Holidays As Range = Nothing) As Double
Dim D As Long
Dim D1 As Long
Dim D2 As Long
Dim T1 As Double
Dim T2 As Double
Dim Total As Double

Const DayStart As Double = 9 / 24
Const DayEnd As Double = 17 / 24
Const FullDay As Double = DayEnd - DayStart

D1 = Fix(Date1)
T1 = Date1 - D1
If T1 = 0 Then
'no time given: default to START of workday
T1 = DayStart
Else
'adjust to time within the workday
If T1 < DayStart Then T1 = DayStart
If T1 > DayEnd Then T1 = DayEnd
End If

D2 = Fix(Date2)
T2 = Date2 - D2
If T2 = 0 Then
'no time given: default to END of workday
T2 = DayEnd
Else
'adjust to time within the workday
If T2 < DayStart Then T2 = DayStart
If T2 > DayEnd Then T2 = DayEnd
End If

Total = 0
If D2 = D1 Then
If IsWorkday(D1, Holidays) Then Total = T2 - T1
Else
For D = D1 To D2
If IsWorkday(D, Holidays) Then
Select Case D
Case D1 'start date
Total = Total + (DayEnd - T1)
Case D2 'end date
Total = Total + (T2 - DayStart)
Case Else 'days between
Total = Total + FullDay
End Select
End If
Next D
End If

WorkedHours = Total

End Function

Private Function IsWorkday(Dt As Long, _
Optional Holidays As Range = Nothing) As Boolean
IsWorkday = False
If Dt Mod 7 >= 2 Then 'N.B. Sunday -> 1, Saturday -> 0
If Holidays Is Nothing Then
IsWorkday = True
Else
IsWorkday = IsError(Application.Match(Dt, Holidays, 0))
End If
End If
End Function

'~~~~~~~~~~~~~~~~~~~

 

[Daniel.M]

Myrna,

FYI, there are little problems when Date2 falls exactly on midnight.

Date1: Wed 2002-08-21 10:00
Date2: Thu 2002-08-22 00:00
returns 15:00, exp res=7:00


James,

For VBA solutions, there are also in the Google archives:

WorkHours() by Harlan Grove
NetWorkHours() by myself

Regards,

Daniel M.

 

[Myrna Larson]

The code I wrote assumes that if there is no time given, the default is end of
the day, 17:00, and, unless the argument is a string, there isn't a way to
distinguish a time of 0:00 vs not entering a time at all. I would anticipate
incidence of "no time given" to be much greater than starting or ending work
at exactly midnight. One solution is to enter the time as 0:01 rather than
0:00.

This may be a common dilemma. I noticed the last time our insurance was
renewed that the new policy takes effect as 12:01 AM, not 12:00.

 

[Myrna Larson]

And if you don't want to count hours during the Week-End:

Wasn't that the reason he explored NETWORKDAYS in the first place? There is
also the problem of holidays, which my code addressed.

 

[Guest]

Daniel, Myrna, Ron,

Thank you all very much, your efforts on this matter have been much appreciated. Daniel, your excel formula works BRILLIANTLY!!

Thanks again.
James.

=MAX($V$4,MIN($V$5,MOD(O2,1)))-MAX($V$4,MIN($V$5,MOD(L2,1)))-
($V$4-$V$5)*(INT(O2)-INT(L2))

Format as [h]:mm:ss

And if you don't want to count hours during the Week-End:

=IF(L2>O2,0,NETWORKDAYS(L2,O2)*($V$5-$V$4)
-IF(NETWORKDAYS(L2,L2),MAX(0,MIN($V$5,MOD(L2,1))-$V$4),0)
-IF(NETWORKDAYS(O2,O2),MAX(0,$V$5-MAX(MOD(O2,1),$V$4)),0))

Regards,

Daniel M.

 

[Daniel.M]

There is also the problem of holidays, which my code addressed.

You can add the holidays argument to the NETWORKDAYS() call if you wish and
it'll take care of those.

Regards,

Daniel M.

 

[Daniel.M]

Myrna,

...

One solution is to enter the time as 0:01 rather than 0:00.

Sorry but the solution is to fix your code. Not asking the user to change his
info.

One way:
If T2 = 0 And Date2 = 0 Then

Regards,

Daniel M.

 

[Daniel.M]

James,

You're welcome.

Daniel M.

Daniel, Myrna, Ron,

Thank you all very much, your efforts on this matter have been much

appreciated. Daniel, your excel formula works BRILLIANTLY!!

 

[Myrna Larson]

I think the "fix" is to ask the user how he wants the function to work, then
write the code accordingly <g>.

I accomodated the possiblity of the user entering just a date without a time,
and defaulting the times to the start and end of the workday. Evidently you
don't think I should have done that.

I don't understand your proposed "fix" below, specifically

Date2 = 0

AIR, the earliest possible date in an Excel worksheet is Jan 1, 1900, and it
has a serial number of 1, not 0. You can enter the number 0 and format the
cell as date, but you see the nonsense result of Jan 0, 1900.

As far as an ending time of midnight, if you enter the date/time as 7/1/2004
24:00:00, Excel jumps in and changes it to 7/2/2004 0:00:00, because time goes
from 0:00:00 to 23:59:59.99999. There's no time equal to 24:00:00.

If you are proposing that when the ending time has a time component of 0, we
should change the time to 1 (equivalent to 24:00:00) and decrement the date,
that's certainly possible. I did that in the code below. But it would be up to
James to say what he wants, not me. In general, I don't like to have the code
make assumptions like this. The user should specify an unambiguous date and
time, i.e. either 7/1/2004 23:59:59 or 7/2/2004 0:00:01.

I have a bit of a problem with the examples James has given: I don't
understand why times outside of the working day are allowed in the first
place. They don't "count" toward the hours worked. Why not have the function
return an error if either time is outside the range 09:00 to 17:00 ?

James prefers the worksheet function approach, so the whole business is moot,
but here's some code that assumes an ending time of 0 means the end of the
previous day rather than the start of the specified day.

Option Explicit

Function WorkedHours(Date1 As Double, Date2 As Double, _
Optional Holidays As Range = Nothing) As Double
Dim D As Long
Dim D1 As Long
Dim D2 As Long
Dim T1 As Double
Dim T2 As Double
Dim Total As Double

Const DayStart As Double = 9 / 24
Const DayEnd As Double = 17 / 24
Const FullDay As Double = DayEnd - DayStart

D1 = Fix(Date1)
T1 = Date1 - D1
'if the starting time is 0, it will be adjusted to 9:00 on Date1
'adjust to time within the workday
If T1 < DayStart Then T1 = DayStart
If T1 > DayEnd Then T1 = DayEnd

D2 = Fix(Date2)
T2 = Date2 - D2

'if the ending time is 0, it will be adjusted to 17:00 on
'the previous day (Date2 - 1)
If T2 = 0 Then
T2 = 1
D2 = D2 - 1
End If

'adjust to time within the workday
If T2 < DayStart Then T2 = DayStart
If T2 > DayEnd Then T2 = DayEnd

Total = 0
If D2 = D1 Then
If IsWorkday(D1, Holidays) Then Total = T2 - T1
Else
For D = D1 To D2
If IsWorkday(D, Holidays) Then
Select Case D
Case D1 'start date
Total = Total + (DayEnd - T1)
Case D2 'end date
Total = Total + (T2 - DayStart)
Case Else 'days between
Total = Total + FullDay
End Select
End If
Next D
End If

WorkedHours = Total

End Function

Private Function IsWorkday(Dt As Long, _
Optional Holidays As Range = Nothing) As Boolean
IsWorkday = False
If Dt Mod 7 >= 2 Then 'N.B. Sunday -> 1, Saturday -> 0
If Holidays Is Nothing Then
IsWorkday = True
Else
IsWorkday = IsError(Application.Match(Dt, Holidays, 0))
End If
End If
End Function

 

[Daniel.M]

Myrna,

<snip>
I don't understand your proposed "fix" below, specifically

Date2 = 0

I interpreted your "no time given" as an empty cell, whereas you meant a cell
with only the date. So discard that completely.

If you are proposing that when the ending time has a time component of 0, we
should change the time to 1 (equivalent to 24:00:00) and decrement the date,
that's certainly possible. I did that in the code below.
<snip>
The user should specify an unambiguous date and
time, i.e. either 7/1/2004 23:59:59 or 7/2/2004 0:00:01.

That's where our vision differ. My position is that checkin and checkout times
(Date1 and Date2) are full timestamps _without_ any special treatment of the
case where one/both of them occur on midnight (or any specific time for that
matter).
But I do understand that _IF_ you have to treat a date without time (so having 0
for time) differently, that's the way you have to proceed.

I have a bit of a problem with the examples James has given: I don't
understand why times outside of the working day are allowed in the first
place. They don't "count" toward the hours worked. Why not have the function
return an error if either time is outside the range 09:00 to 17:00 ?

These kind of time calculation problems occur in a variety of context. One of
which is to answer the following problem: A machine breaks at T1 (full
timestamp : date+time) and is repaired at T2 (again full timestamp). What is the
missing production time (between 8:00 and 17:00) not counting
week-ends/holidays? Because the machine can break anytime during the day, you
need the calcs we're talking about.
Also, even from a 'workers' timesheet perspective, we frequently want to pay the
hours differently during specific period (8:00 to 17:00) when some work is done
inside the period and some work is done outside.

but here's some code that assumes an ending time of 0 means the end of the
previous day rather than the start of the specified day.

Thanks for the code.

Regards,

Daniel M.

 

[Myrna Larson]

I interpreted your "no time given" as an empty cell, whereas you meant a cell

with only the date. So discard that completely.

Yes, I meant a date without a time.

That's where our vision differ. My position is that checkin and checkout times
(Date1 and Date2) are full timestamps _without_ any special treatment of the
case where one/both of them occur on midnight (or any specific time for that
matter).

If you want to REQUIRE that both a date and time be specified, then certainly
there's no reason to modify the user's entry in any way other than to shift it
to the beginning or end of the work day.

But I do understand that _IF_ you have to treat a date without time (so having 0
for time) differently, that's the way you have to proceed.
"It's not a bug, it's a feature." <g>

Yes, that's what I intended -- the "feature" that you didn't have to specify a
time if you want to include the entire working day. But it does have the
drawback that when you DO specify a time, you can't use EXACTLY 0:00:00,
because there's no way to distinguish between that time and
"time-not-specified".

These kind of time calculation problems occur in a variety of context. One of
which is to answer the following problem: A machine breaks at T1 (full
timestamp : date+time) and is repaired at T2 (again full timestamp). What is the
missing production time (between 8:00 and 17:00) not counting
week-ends/holidays? Because the machine can break anytime during the day, you
need the calcs we're talking about.

To be a bit "picky", it would seem to me that the machine would only break
during the time it's in use, i.e. during regular working hours. But the end
time could be outside of regular hours, assuming you call in the repairmen and
they work during the evening until it's fixed.

Also, even from a 'workers' timesheet perspective, we frequently want to pay the
hours differently during specific period (8:00 to 17:00) when some work is done
inside the period and some work is done outside.

That was my puzzlement with this problem: rather than ignore hours outside the
regular day -- "throwing them away" so to speak -- if we are talking about
hours worked, those hours would in most businesses generate EXTRA pay, not NO
pay. In the past (at least several years ago) I've posted formulas and/or code
to calculate the pay for regular hours and overtime.