Calculate workdays, excluding weekends (Days elapsed)

Options

I found the solution for "How to calculate days a ticket is open/closed" on your site (solution submitted by Andrée Starå), but, I need the formula to count just the weekdays, excluding weekends. I tried a couple of different ways, but cannot get it to work properly.

Any help would be greatly appreciated!

This is the formula that works, but I need to count just the weekdays

=IF([Project Completion Date]@row <> "", [Project Completion Date]@row - [Project Start Date]@row, IF([Project Start Date]@row <> "", TODAY() - [Project Start Date]@row))



Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓
    Options

    Either of these should work. They just approach the logic from a different direction.


    =IF([Project Completion Date]@row <> "", NETWORKDAYS([Project Start Date]@row, [Project Completion Date]@row), NETWORKDAYS([Project Start Date]@row, TODAY()))


    =IF([Project Completion Date]@row = "", NETWORKDAYS([Project Start Date]@row, TODAY()), NETWORKDAYS([Project Start Date]@row, [Project Completion Date]@row))

Answers

  • Seneca
    Seneca ✭✭
    Options

    Hi Paul, thank you for the feedback, but, I don't want to "return the number of working days between two dates"

    This is for Days Elapsed (no end date yet) . As you see in the above screenshot, there is no Project Completion Date, but it still counts the days Days Elapsed regardless.

    The formula I show above works fine and I did notice that the Days Elapsed in my SmartSheet automatically increased by one day from yesterday to today, but I dont want to count weekend days.

    Thank you again, hope you can help!

    If you have a solution, could you show me how it would be written? I am not good at creating formulas, but I am good at finding them :) and adjusting accordingly, depending on how convoluted they are.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Right. Instead of Date minus Date you would use the NETWORKDAYS function. The other "date" inside of that can be TODAY.


    So instead of saying [Completion Date] - [Start Date] or TODAY - [Start Date] depending on the true/false from the IF, you would say NETWORKDAYS between [Completion Date] and [Start Date] or NETWORKDAYS between TODAY() and [Start Date].

  • Seneca
    Seneca ✭✭
    Options

    Thanks Paul, but I am completely lost. As I mentioned, I'm not good at writing formulas, I'm good at finding them and adjusting, but I still don't understand what this formula should look like. I tried your info from above but it's not working, as I know I'm not inserting the right info in the right context.


    Thank you anyway

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓
    Options

    Either of these should work. They just approach the logic from a different direction.


    =IF([Project Completion Date]@row <> "", NETWORKDAYS([Project Start Date]@row, [Project Completion Date]@row), NETWORKDAYS([Project Start Date]@row, TODAY()))


    =IF([Project Completion Date]@row = "", NETWORKDAYS([Project Start Date]@row, TODAY()), NETWORKDAYS([Project Start Date]@row, [Project Completion Date]@row))

  • Seneca
    Seneca ✭✭
    Options

    Thank you Carson, they both worked beautifully!

    Woohoo!

    After all these years of researching formulas for different scenarios and adjusting accordingly, this one had me stumped.

    Thank you again!