# Day of the year

Is there a formula that results in the day of the year?
January 31st is the 31st day of the year.
December 31st is the 365th in a non leap year
December 31st is the 366th in a leap year

Thanks, Tracey

``````=TODAY()-DATE(YEAR(TODAY());1;1)+1
=B2-DATE(YEAR(B2);1;1)+1
leap-year:
=DATEVALUE("2012-12-31")-DATE(2012;1;1)+1
``````
``````=DATEDIF("1899-12-31";A1;"yd")
or
=A1-EOMONTH(A1;-MONTH(A1))
``````
1 Like

If a tax is introduced on the length of the formula:

``````=A1-DATE(YEAR(A1);1;)
``````

Try:

``````=DAYS(A1, DATE(YEAR(A1),1,1))+1
``````

I just pasted your formula in and the result is 364
Today is the 46th day of the year (31+15)
I tried numerous other ways before, but could not get it to work.
Thanks, Tracey

We can only guess what your A1 containsâ€¦ If you seek help, not pity upload your sheet here, so we can check.

Develop it yourself:

• Put in A1 `=TODAY()`
• B1 set to 2024-01-01
• Format both cells as numbers, to see the dates are internally only numbers. The one in A1 should be greater than B1
• Change formatting back to date (thus changes what is shown, not the internal value).
• Write in C1 the formula `=A1-B1` You will realize, the shown difference is of by one from your target, as B1 is already the first day of the year.
• Change the formula to `=A1-B1+1`

What is left would be a way to do this for any year, so we need to use the year of the date in A1 instead of a fixed date in B1. This was already shown by @Lupp above with B2, wich I changed for A1

• `=A1-DATE(YEAR(A1);1;1)+1`
1 Like

I ended up using: DayOfYear=TODAY()-DATE(YEAR(TODAY());1;1)+1
It appears easier to read with little explanation.

I also created the inverse: =DayOfYear+DATE(YEAR(TODAY());1;1)-1

Just FYI, Tracey