Networkdays Alternative, with Time

Merry Christmas All!

I'm trying to track down an alternative to the NETWORKDAYS formula because for some bizarre reason we aren't allowed to install the Analysis Toolpak at work.

I've got this formula, which works pretty good....

    =SUMPRODUCT(--(COUNTIF(HolidayList,ROW(INDIRECT(INT(E3)&":"&TODAY())))=0), --(WEEKDAY(ROW(INDIRECT(INT(E3)&":"&TODAY())),3)<5))

...but it only counts whole days. What I'm after is something that takes the time into account as well. Any ideas?

Cheers,

Wayne
LVL 48
Wayne Taylor (webtubbs)Asked:
Who is Participating?
 
barry houdiniCommented:
Well, I think you can just use your existing formula with an adjustment for the times. That formula is effectively counting from 12:00 AM on the date in E3 up to the end of today so you can just subtract those times, i.e.
=SUMPRODUCT(--(COUNTIF(HolidayList,ROW(INDIRECT(INT(E3)&":"&TODAY())))=0),--(WEEKDAY(ROW(INDIRECT(INT(E3)&":"&TODAY())),3)<5))-MOD(E3,1)+MOD(NOW(),1)-1
format result cell as [h]:mm
That works assuming today isn't a weekend or holiday. If you want to allow that possibility change to
=SUMPRODUCT(--(COUNTIF(HolidayList,ROW(INDIRECT(INT(E3)&":"&TODAY())))=0),--(WEEKDAY(ROW(INDIRECT(INT(E3)&":"&TODAY())),3)<5))-MOD(E3,1)+(MOD(NOW(),1)-1)*(WEEKDAY(TODAY(),3)<5)*(COUNTIF(HolidayList,TODAY())=0)
regards, barry
0
 
barry houdiniCommented:
Hello Wayne,
So given that formula I take it you need to account for holidays? Are you counting all hours of the working days or do you just want to count between specific times?
You have start date and time in E3 I presume so you are taking that time into account but not the end time (just TODAY()?)
Could the start time/date be on a holiday or weekend?
regards, barry
0
 
Wayne Taylor (webtubbs)Author Commented:
Hi Barry,

Yes, I need to account for holidays. I basically need to discount the entire 24 hours of any holiday or weekend days.

I had the TODAY() function in the formula because it will fail if I use NOW() because it needs an integer, but I will need to use NOW() in the final formula.

The start time will never be on a weekend or holiday.

Cheers,

Wayne
0
 
Wayne Taylor (webtubbs)Author Commented:
Excellent! Thanks Barry!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.