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
Please advise.
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-B1You 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