Exclude weekends from date calculation

Hello,

Is there a way to exclude weekends from a date calculation? I have an application with start and end dates and I want to calculate the number of days between the start and end date, not counting weekends.

I have this formula working but it includes weekends so if someone takes a Friday and a Monday the calculation shows 4 days instead of 2.

=IF(AND([End Date]@row - [Start Date]@row = 0, [PTO Type]@row = "PTO - Half Day"), 0.5, IF(AND([End Date]@row - [Start Date]@row = 0, [PTO Type]@row <> "PTO - Half Day"), 1, ([End Date]@row - [Start Date]@row) + 1))

Thanks!

Andrea

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Have you looked into the NETWORKDAY function?

Paul Newcome

If the start date is a non-working day, then the NETWORKDAY function will add 1. You may want to double check that start date.

All comments

Paul Newcome

Have you looked into the NETWORKDAY function?

Andrea Zenner

That seems to work! I have one entry for which it's counting 1 weekend day but I'll figure that out.. it is working for the other entries.

Thanks much @Paul Newcome !

Paul Newcome

If the start date is a non-working day, then the NETWORKDAY function will add 1. You may want to double check that start date.

Andrea Zenner

@Paul Newcome That was the problem! We'll just instruct people to not enter start dates on a weekend. Thanks again!

Paul Newcome

Great! Happy to help. 👍️

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.