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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Have you looked into the NETWORKDAY function?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!