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
Andrea Zenner
Program Manager | Infrastructure & Operations
Apogee Enterprises
EAP | Mobilizer | Core Product Certified | Superstar
azenner@apog.com
Best Answers
-
Have you looked into the NETWORKDAY function?
-
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.
Answers
-
Have you looked into the NETWORKDAY function?
-
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 !
Andrea Zenner
Program Manager | Infrastructure & Operations
Apogee Enterprises
EAP | Mobilizer | Core Product Certified | Superstar
azenner@apog.com
-
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.
-
@Paul Newcome That was the problem! We'll just instruct people to not enter start dates on a weekend. Thanks again!
Andrea Zenner
Program Manager | Infrastructure & Operations
Apogee Enterprises
EAP | Mobilizer | Core Product Certified | Superstar
azenner@apog.com
-
Great! Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!