Excel Networkdays w/hours and minutes 1

[wonderwhy]

Hello. I am using the Networkdays function in Excel to calculate the difference between two dates based on working days only (m-f). I am also using a named range called 'holidays' which contains dates which should not be counted. Here's the syntax:

=NETWORKDAYS(C10, E10,Holidays)

That works great, except I would really like to change my date fields from mm/dd/yyyy to mm/dd/yyyy hh:ss and be able to have some way of calculating down to the hour (minutes would be OK, too, but not necessary). Even just a total number of hours would be great(skip days altogether), if that would be easier.

Has anyone done this who would like to share? Any help is appreciated.

Thanks,

Julia

 

[ilses]

Julia,

Format the result cell with a custom format:

[h]

to show the difference in hours.

HTH

Ilse

 

[wonderwhy]

I tried to do that, but I haven't been able to figure out how. Where would I do that?

Thanks,

Julia

 

[AnotherHiggins]

Right click, go to Format Cells
On the Number tab, choose Custom from the list on the left
In the Type field on the right, type
[h]
Including the brackets tells Excel to include numbers greater than 24 (since 24 hours = 1 day).

I would add a caveat, however. This will display the total number of hours - for 24 hours per day. If you are excluding weekends and holidays, don't you also want to exclude non-business hours?

If you are interested in doing that, post back for a solution.



[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[wonderwhy]

Thank you Ilse and John. I tried what you suggested, John. I changed the format of my date fields from mm/dd/yyyy to mm/dd/yyyy hh:ss and then added the custom format to the calculated field of [h]. This made the calculated field show 24 instead of 1 (it had been showing days). But, it is really just converting the 1 into hours and disregarding the time part of the date field altogether. For example:

1/18/05 8:00 for the start time, and...
1/18/05 16:00 for the end time

using the =NETWORKDAYS(D10, G10,Holidays)formula still shows 24 hours even though it should be 9 hours.

I'm not too worried about excluding non-business hours. Although, if you have time sometime I'd love to see how to do that, too. Right now, I just need the number of hours difference between the two dates.

Thanks again for your help.

Julia

 

[wonderwhy]

Hello,

Does anyone know a fairly simple way to get the number of hours between two dates excluding weekends and holidays?

Thanks,

Julia

 

[wonderwhy]

John (anotherhiggins),

It turns out I do need to exclude non-business hours, too. If you have a solution I'd appreciate some help.

Thank you,

Julia

 

[TonyJollans]

Hi wonderwhy,

This should do it. If the first day is either a weekend or a holiday it ignores the whole day, else it just ignores the hours before the time; similarly for the last day.

Assuming start date/time in A1 and end date/time in B1:

[blue][tt]=NETWORKDAYS(A1,B1,H1:H2)
-IF(NETWORKDAYS(A1,A1,H1:H2)=0,1,MOD(A1,1))
-IF(NETWORKDAYS(B1,B1,H1:H2)=0,0,(1-MOD(B1,1)))[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[wonderwhy]

I will try to get this to work and post back with the results. Thank you, Tony!

 

[wonderwhy]

Thank you, Tony. That works great! Here's how my function ended up looking...

=NETWORKDAYS(D9,F9,Holidays)-IF(NETWORKDAYS(D9,D9,Holidays)=0,1,MOD(D9,1))-IF(NETWORKDAYS(F9,F9,Holidays)=0,0,(1-MOD(F9,1)))

I used a custom format for the cells--> [h]:mm

Julia

Thanks to John, too! You have both made my day.

 

[wonderwhy]

Can anyone help me with adding to the formula so that it excludes non-business hours (like from 5PM - 8AM)?

Thank you.

Julia

 

[pswaroop]

Hi Julia

I am also looking for the same ans. Did you get answer to your "excludes non-business hours (like from 5PM - 8AM)?" question?

Thanks
Prashant

 

[TonyJollans]

Hi Julia,

Sorry for not replying before.

1. Add a Name StartTime, Value [blue]=TimeValue("08:00")[/blue] (or whatever your start time is)
2. Add a Name EndTime, Value [blue]=TimeValue("17:00")[/blue] (or whatever your end time is)
3. Assuming you still have Holidays defined as a Name.
4. Assuming your start and end dates are in A2 and B2.

[blue][tt]NETWORKDAYS(A2,B2,Holidays)[/tt][/blue] gives the total number of work days including both the start and end of the range if they are work days.

[blue][tt](NETWORKDAYS(A2,B2,Holidays)) * (EndTime-StartTime)[/tt][/blue] (formatted as [h]:mm) gives the total number of work hours in those days.

[blue][tt]MAX(0,EndTime-MAX(StartTime,MOD(A2,1)))[/tt][/blue] gives the number of work hours in the start date but, if it is a work day, a full day has already ben included in the NETWORKDAYS result above. You need to subtract a day and add the actual number of hours, thus:

[blue][tt] MAX(0,EndTime-MAX(StartTime,MOD(A2,1)))-EndTime+StartTime[/tt][/blue]

(Note that this will produce a negative time value and not display on its own). The adjustment is only required if the start date is a work day, so:

[blue][tt] IF(NETWORKDAYS(A2,A2)=0,
0,
MAX(0,EndTime-MAX(StartTime,MOD(A2,1)))-EndTime+StartTime)[/tt][/blue]

gives the adjustment for the first day.

Similarly:

[blue][tt] IF(NETWORKDAYS(B2,B2)=0,0,MAX(0,MIN(MOD(B2,1),EndTime)-StartTime))-EndTime+StartTime)[/tt][/blue]

gives the adjustment for the last day.

Putting it all together gives (if I've got this right!):

[blue][tt]=(NETWORKDAYS(A2,B2,Holidays)) * (EndTime-StartTime)
+ IF(NETWORKDAYS(A2,A2,Holidays)=0,0,MAX(0,EndTime-MAX(StartTime,MOD(A2,1)))-EndTime+StartTime)
+ IF(NETWORKDAYS(B2,B2,Holidays)=0,0,MAX(0,MIN(MOD(B2,1),EndTime)-StartTime)-EndTime+StartTime)[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[