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

autumnleaves
autumnleaves ✭✭
in Formulas and Functions

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?

· Share on FacebookShare on Twitter

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    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:

    image.png

    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!

    · Share on FacebookShare on Twitter

Answers

  • Andrée Starå
    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!

    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.

    · Share on FacebookShare on Twitter
  • autumnleaves
    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?

    · Share on FacebookShare on Twitter
  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    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:

    image.png

    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!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!