How can I add a column to subtract weekends from the overall date range

I have a mix bag of vendors doing work, some have 7 day work weeks while other have 5 day work weeks, and I need them all laid out on the same Calendar.

I haven't found a way to mesh those two together nicely, so my though process was to: set up the work week as 7 days, and add a New column left of the "Duration column: (total days of work, weekends included)", but in this new column it would subtract all the weekend days (giving me the actual working days for my Vendors who only work 5 days a week)


I'm not even sure if this is possible, but any help would be greatly appreciated!

Best Answer

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 03/04/22

    @bordemkills

    You can use the NETWORKDAYS function. It excludes Saturday and Sunday from the count.

    Syntax: NETWORKDAYS(start_date, end_date, [ holidays ])

    • start_date — The first date to be measured
    • end_date — The last date to be measured
    • holidays —[optional] The dates to exclude from the count

    Holidays can also reference a range from a reference sheet listing dates that are holidays.

    So in your case:

    =NETWORKDAYS([Start Date]@row, [End Date]@row)

    or, if you don't have an end date yet, you can do elapsed days:

    =NETWORKDAYS([Start Date]@row, TODAY())

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Thanks for the reply Jeff, I tried out the NetworkDays function and it doesn't seem to take out the weekends for me it just duplicates what's in the Duration Column. I didn't need to mess with Holidays. Do you think I'm missing a setting or something? (My work weeks are set up for 7 days in the settings)



  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @bordemkills

    Yes, you have to set Working Days as Mon-Fri in Admin Center, otherwise it counts Sat and Sun as working days and will include them in NETWORKDAYS. It's advisable to do this, since a simple subtraction of one date from another date will include every day including weekends, for those vendors with 7-day work weeks.


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!