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!

Find more posts tagged with

Accepted answers

Jeff Reisman

@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.

All comments

Jeff Reisman

@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())

bordemkills

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

@bordemkills

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.