Is it possible to format dates as decimal year? Or will this require additional steps than simple formatting? For example, D MMM YYYY works very nicely for displaying the date, but YYYY.YYYY not so much:
Not format but calculate, assuming date in A1:
=YEAR(A1)+(A1-DATE(YEAR(A1);1;1))/IF(ISLEAPYEAR(A1);366;365)
3 Likes
Thank you so much! That perfect!
maybe:
=YEAR(A1)&"."&MID(YEARFRAC(DATE(YEAR(A1);1;0);A1;0);3;9)
#or
=YEAR(A1)+YEARFRAC(DATE(YEAR(A1);1;0);A1;0)
1 Like
Hmm… trying this manually, and starting with https://www.timeanddate.com/date/weekday.html
1805 + (107/365) = 1805.29315068493
Hallo
=YEAR(A1)+YEARFRAC(DATE(YEAR(A1);1;1);A1;3)
1 Like
And that’s the difference. It’s the 107th day in the year, but my formula calculates it as 106th day, because otherwise 1805-12-31 would yield 1806 instead of 1805.99726027397
Fwiw, I do not see any difference between my formula and karolus’ second formula or third, all yield 1805.2904109589 for 1805-04-17.
1 Like
Thank you both for this help, it’s very much appreciated!