Networkdays Alternative, with Time

Posted on 2009-12-29
Last Modified: 2012-05-08
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....


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


    LVL 50

    Expert Comment

    by:barry houdini
    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
    LVL 46

    Author Comment

    by:Wayne Taylor (webtubbs)
    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.


    LVL 50

    Accepted Solution

    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.
    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
    regards, barry
    LVL 46

    Author Closing Comment

    by:Wayne Taylor (webtubbs)
    Excellent! Thanks Barry!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now