Excluding Weekends from Date formulas?

Hi,

I'm hoping this is possible and someone can guide me, but I am trying to automatically exclude weekends from date formulas.

In my sheet I have a duration column (which is calculated based on a number of other columns: task performed, difficulty level of task, etc) and then a start/end date column which calculates on how long the duration will be. Note, the duration column is simply titled duration, it is not a "duration" column type.

However, when dates expand over the weekends I would want them excluded seeing is this is finding start/end work days.

Thanks, any help is appreciated!

Find more posts tagged with

Formulas

Accepted answers

Carson Penticuff

Okay, I think I get what you are saying now. These two formulas would replace the ones in your screenshots.

=IFERROR(WORKDAY([Start Date]11, -CEILING([Delay (Days)]@row + [Duration (Days)]@row, 1)), "")

=IFERROR(WORKDAY([Start Date]@row, CEILING([Delay (Days)]@row + [Duration (Days)]@row, 1)), "")

All comments

Carson Penticuff

Am I understanding correctly that the end date is being calculated by adding the duration to the start date?

How do you want to handle partial days? Should they always round up?

Jezza

Correct, for partial days I think rounding would be okay, thanks.

Carson Penticuff

This will always round to the next day, no rounding down.

=IFERROR(WORKDAY([Start Date]@row, CEILING([Duration]@row, 1)),"")

Jezza

Thank you!

To complicate it further, the project type is dependent upon two specific tasks/dates, which will be manually entered (highlighted in yellow); everything that should happen before it, duration is subtracted; everything after, added, as such:

Would I insert my existing formulas into yours to replace [Duration]?

Carson Penticuff

I'm afraid I'm not following exactly how you have this setup. Are all of the durations and dates based on the two manually entered dates?

Jezza

Exactly, they essentially waterfall forward and backwards in time setting the start dates based on how long each task will take, derived from the duration formula.

Carson Penticuff

Okay, I think I get what you are saying now. These two formulas would replace the ones in your screenshots.

=IFERROR(WORKDAY([Start Date]11, -CEILING([Delay (Days)]@row + [Duration (Days)]@row, 1)), "")

=IFERROR(WORKDAY([Start Date]@row, CEILING([Delay (Days)]@row + [Duration (Days)]@row, 1)), "")

Jezza

Huzzah, thanks so much, Carson, works like a charm!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.