Create a sheet that lists all working days in the date column
![Album](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
I am creating a template for a daily log for our job sites. I want to find a quick way to fill in our working days into the rows so I'm not manually typing or clicking and dragging and deleting every weekend day, does anyone have a formula for making this happen?
Best Answer
-
@Album This formula will cut out Saturdays and Sundays. Start by setting your first date in Row 1 manually, then below it enter:
=IF(WEEKDAY(Date1) = 6, Date1 + 3, IF(WEEKDAY(Date1) = 7, Date1 + 2, Date1 + 1))
Then just Ctrl-C/Ctrl-V this formula down through 260 or so more rows and you'll have all the week days for a year.
The logic is: If the day of the week for the date in the row above this one equals 6, aka Friday, add 3 days to that date; but if the day of the week for the date in the row above this one equals 7, aka Saturday, add 2 days to that date; otherwise, just add 1 to that date.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
@Album This formula will cut out Saturdays and Sundays. Start by setting your first date in Row 1 manually, then below it enter:
=IF(WEEKDAY(Date1) = 6, Date1 + 3, IF(WEEKDAY(Date1) = 7, Date1 + 2, Date1 + 1))
Then just Ctrl-C/Ctrl-V this formula down through 260 or so more rows and you'll have all the week days for a year.
The logic is: If the day of the week for the date in the row above this one equals 6, aka Friday, add 3 days to that date; but if the day of the week for the date in the row above this one equals 7, aka Saturday, add 2 days to that date; otherwise, just add 1 to that date.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Awesome thanks! That worked perfectly.
-
Had to reread the part about entered the 1st date manually… and then formula worked perfect! So helpful, thank you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!