Is it possible to have a date counter formula in a sheet?
I am making spend reports and it would be helpful to be able to automate certain things. In this instance, the number of trading days left in a month. Could it be automated to count down each day if I were to set it at the beginning of the month?
Best Answer
-
Hi @autumnleaves,
You can absolutely do something like this. If metric is only for current month, you can set this once and not need to update it and wanting today number of days (every day is a trading day):
=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()) + 1, 1, 1) - 1) - TODAY()
The IFERROR part of the formula calculates the end of the month, then subtracts todays date.
If you wanted it for a 5 day working week:
=NETWORKDAYS(TODAY(), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()) + 1, 1, 1) - 1))
You can add a range between the last brackets listing any days to exclude (e.g. public holidays), which you would probably put on a reference sheet (or a hidden column). For example:
=NETWORKDAYS(TODAY(), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()) + 1, 1, 1) - 1), [Public holidays]:[Public holidays])
Sample data using UK public holidays (with slightly fudged Easter date to demonstrate the difference between the 2nd/3rd formulas:
This is in a single sheet, but you could use cross sheet references instead to have the public holidays list elsewhere.
Hope this helps, but if you've any problems/questions then just let us know!
Answers
-
I hope you're well and safe!
Yes, it is. How would you decide the number of trading days?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Well if the number was set each month, being the total number of days minus weekends and any holidays, it would be good if I could write a formula/use a function to then count down for me each day to show the remaining days.
Do you know how I could write a formula like that?
-
Hi @autumnleaves,
You can absolutely do something like this. If metric is only for current month, you can set this once and not need to update it and wanting today number of days (every day is a trading day):
=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()) + 1, 1, 1) - 1) - TODAY()
The IFERROR part of the formula calculates the end of the month, then subtracts todays date.
If you wanted it for a 5 day working week:
=NETWORKDAYS(TODAY(), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()) + 1, 1, 1) - 1))
You can add a range between the last brackets listing any days to exclude (e.g. public holidays), which you would probably put on a reference sheet (or a hidden column). For example:
=NETWORKDAYS(TODAY(), IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()) + 1, 1, 1) - 1), [Public holidays]:[Public holidays])
Sample data using UK public holidays (with slightly fudged Easter date to demonstrate the difference between the 2nd/3rd formulas:
This is in a single sheet, but you could use cross sheet references instead to have the public holidays list elsewhere.
Hope this helps, but if you've any problems/questions then just let us know!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!