Formula for today's date as day ___ of 365?

Hi there!

I need to make a simple dashboard metric widget displaying the current day's date as day ___ of 365, as a percentage.

Even more specifically, if possible, I need to do this same thing for today's date as day ___ of [number of days in current quarter], as a percentage.

Examples:

Today is Feb 18, which is day 49 of the year (13%) or day 49 of 90 in Q1 (54%).

July 4 would be day 185 of the year (51%) or day 4 of 92 in Q3 (4%).

Any tricks for this?

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

That should work as well, but it may not be quite as accurate in the long term because of leap years (very minor).

You could also take each of your formulas and use the nested IF idea above so that you don't have to change the widget source each quarter.

Paul Newcome

Ok. Lets try throwing some extra parenthesis around to see if we can make sure all numeric functions are isolated within the text string...

="Day " + (YEARDAY(TODAY()) - YEARDAY(DATE(2021, 1, 10))) + " of " + (YEARDAY(DATE(2021, 3, 10)) - YEARDAY(DATE(2021, 1, 10))) + " (" + (ROUND((YEARDAY(TODAY()) - YEARDAY(DATE(2021, 1, 10))) / (YEARDAY(DATE(2021, 3, 10)) - YEARDAY(DATE(2021, 1, 10))), 2) * 100) + "%)"

All comments

Paul Newcome

To get the current number of days...

=YEARDAY(TODAY())

The number of days in the year (to account for leap years)

=YEARDAY(DATE(YEAR(TODAY()), 12, 31))

To get the percentage we divide the current by the total.

="(" + ROUND(YEARDAY(DATE(YEAR(TODAY()), 12, 31)) / YEARDAY(TODAY()), 2) * 100 + "%)"

So to pull all of that together:

="day " + YEARDAY(TODAY()) + " of " + YEARDAY(DATE(YEAR(TODAY()), 12, 31)) + "(" + ROUND(YEARDAY(DATE(YEAR(TODAY()), 12, 31)) / YEARDAY(TODAY()), 2) * 100 + "%)"

Jennifer Sizelove

Thanks for that breakdown, @Paul Newcome. I never would have gotten any of those pieces on my own, much less the whole string! It's nice to get a bit of teaching instead of just a final answer, much appreciated. I did swap the order of the second part for the valid percentage:

="Day " + YEARDAY(TODAY()) + " of " + YEARDAY(DATE(YEAR(TODAY()), 12, 31)) + " (" + ROUND(YEARDAY(TODAY()) / YEARDAY(DATE(YEAR(TODAY()), 12, 31)), 2) * 100 + "%)"

However, I'm still stuck on how to do the same thing by the current quarter instead of the whole year?

Paul Newcome

Sorry about that. Apparently I need a little more coffee. Haha.

The first quarter is a little easier. You would simply change the date for 31 December to the last day of the first quarter.

Thinking more on how to get the other quarters...

What if we subtract the YEARDAY of the first date in the quarter from the YEARDAY of TODAY? That should give us how many days we are into the quarter. Then use the same concept as before to establish the end of the quarter and use that for your percentage.

YEARDAY(TODAY()) - YEARDAY(DATE(first day of 2nd quarter))

YEARDAY(DATE(last day of 2nd quarter)) - YEARDAY(DATE(first day of 2nd quarter))

(YEARDAY(TODAY()) - YEARDAY(DATE(first day of 2nd quarter))) / (YEARDAY(last day of 2nd quarter) - YEARDAY(DATE(first day of 2nd quarter)))

="Day " + YEARDAY(TODAY()) - YEARDAY(DATE(first day of 2nd quarter)) + " of " + YEARDAY(DATE(last day of 2nd quarter)) - YEARDAY(DATE(first day of 2nd quarter)) + " (" + ROUND((YEARDAY(TODAY()) - YEARDAY(DATE(first day of 2nd quarter))) / (YEARDAY(last day of 2nd quarter) - YEARDAY(DATE(first day of 2nd quarter))), 2) * 100 + "%)"

The most straightforward way to automate all of this for the quarters into a single formula would be to use a nested IF.

First put each of your four different quarter formulas into their own cells.

Then we can copy/paste each one into the appropriate portion of the nested IF.

=IF(TODAY() <= DATE(last day of 1st quarter), first quarter formula, IF(TODAY() <= DATE(last day of 2nd quarter), second quarter formula, IF(TODAY() <= DATE(last day of 3rd quarter), third quarter formula, fourth quarter formula)))

Make sense?

Jennifer Sizelove

All good! That is a whole lot more complex than what I just came up with. Based on your experience, which is more likely to run smoother? I made 4 separate quarterly entries on a sheet summary to route to a dashboard widget. I'll have to change the widget source at the turn of each quarter, but here's what I did:

="Day " + YEARDAY(TODAY()) + " of 90 (" + ROUND((YEARDAY(TODAY()) / 90), 2) * 100 + "%)"

="Day " + (YEARDAY(TODAY()) - 90) + " of 91 (" + ROUND(((YEARDAY(TODAY()) - 90) / 91), 2) * 100 + "%)"

="Day " + (YEARDAY(TODAY()) - 181) + " of 92 (" + ROUND(((YEARDAY(TODAY()) - 181) / 92), 2) * 100 + "%)"

="Day " + (YEARDAY(TODAY()) - 273) + " of 92 (" + ROUND(((YEARDAY(TODAY()) - 273) / 92), 2) * 100 + "%)"

Thoughts?

Paul Newcome

That should work as well, but it may not be quite as accurate in the long term because of leap years (very minor).

You could also take each of your formulas and use the nested IF idea above so that you don't have to change the widget source each quarter.

Jennifer Sizelove

@Paul Newcome I ran out of time yesterday so I'm cracking at this today...I plugged in your quarterly equation trying to get Q2 rolling:

="Day " + YEARDAY(TODAY()) - YEARDAY(DATE(2021, 4, 1)) + " of " + YEARDAY(DATE(2021, 6, 30)) - YEARDAY(DATE(2021, 4, 1)) + " (" + ROUND((YEARDAY(TODAY()) - YEARDAY(DATE(2021, 4, 1))) / (YEARDAY(2021, 6, 30) - YEARDAY(DATE(2021, 4, 1))), 2) * 100 + "%)"

It came back as #INVALID OPERATION. I have no idea where I went wrong!!! 😨

Paul Newcome

Lets try this...

="Day " + YEARDAY(TODAY()) - YEARDAY(DATE(2021, 4, 1)) + " of " + YEARDAY(DATE(2021, 6, 30)) - YEARDAY(DATE(2021, 4, 1)) + " (" + (ROUND((YEARDAY(TODAY()) - YEARDAY(DATE(2021, 4, 1))) / (YEARDAY(2021, 6, 30) - YEARDAY(DATE(2021, 4, 1))), 2) * 100) + "%)"

Jennifer Sizelove

Hmm...still showing #INVALID OPERATION.

I changed the quarter date ranges to fit Jan 10-Mar 10 as a test, thinking maybe the Q2 formula didn't work for today's date being out of the Q2 date range:

="Day " + YEARDAY(TODAY()) - YEARDAY(DATE(2021, 1, 10)) + " of " + YEARDAY(DATE(2021, 3, 10)) - YEARDAY(DATE(2021, 1, 10)) + " (" + (ROUND((YEARDAY(TODAY()) - YEARDAY(DATE(2021, 1, 10))) / (YEARDAY(2021, 3, 10) - YEARDAY(DATE(2021, 1, 10))), 2) * 100) + "%)"

But I still got #INVALID OPERATION.

Paul Newcome

I completely missed it the first time around...

The second to last YEARDAY function doesn't have a DATE function in it.

="Day " + YEARDAY(TODAY()) - YEARDAY(DATE(2021, 1, 10)) + " of " + YEARDAY(DATE(2021, 3, 10)) - YEARDAY(DATE(2021, 1, 10)) + " (" + (ROUND((YEARDAY(TODAY()) - YEARDAY(DATE(2021, 1, 10))) / (YEARDAY(DATE(2021, 3, 10)) - YEARDAY(DATE(2021, 1, 10))), 2) * 100) + "%)"

Jennifer Sizelove

I copy/pasted the above, still getting #INVALID OPERATION. I think I broke it.

Paul Newcome

Ok. Lets try throwing some extra parenthesis around to see if we can make sure all numeric functions are isolated within the text string...

Jennifer Sizelove

YEAH PAUL! I have no idea which parentheses were missing but that seems to have done it! Thanks a million!

Paul Newcome

Happy to help. 👍️

What I did was wrapped each of the numeric functions in their own set of parenthesis to make sure they would calculate inside of the text string.

="text" + number_generating_function

="text" + (number_generating_function)

So basically I took

YEARDAY() - YEARDAY

and turned it into

(YEARDAY() - YEARDAY())

Amber Benge

@Jennifer Sizelove I was tracking this thread (@Paul Newcome you’re a beast!). Any chance you’re still using this dashboard and would share a screenshot? Would love to see how you visualized it.

Paul Newcome

@Amber Benge Haha. Thanks.

I think I grabbed the wrong quarter, but if you are using a basic Metrics Widget like @Jennifer Sizelove original mentioned, then it would look something like this...

Yeah... Pretty sure I grabbed the wrong quarter, but that would just change the actual numbers. The visualization would still look the same.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.