Is it possible to have a date counter formula in a sheet?

autumnleaves

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?

Nick Korna

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!

Andrée Starå

Hi @autumnleaves

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!

autumnleaves

https://community.smartsheet.com/discussion/comment/428544#Comment_428544

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?

Nick Korna

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!