How to add fractional year to date in Excel?

Question

Is there a way to add a fractional year to a date in Excel? I have tried something like =date(2000 + 1.2, 1, 1) but the result is 1/1/2001 rather than the expected approximate of 3/14/2001.

Answer 1

You could use:

=A1  +  TRUNC(  IF(  OR(  YEAR(A1) = 2000,  MOD(  YEAR(A1),  4  ))  <>  0,  365,  366  )  *1.2,  0)

The YEAR() function extracts the year involved then lets the MOD() function see if it is the year 2000 or if it does NOT divide by 4. If it meets either condition (2000, or not divisible by 4, you get 365 days to multiply. Otherwise you get 366. That is then multiplied by the 1.2, though anything or any cell could go there. To finish, it is truncated at 0 decimal places so only full days are added, then added to the initial value (assumed to be in A1).

Not too OCD about the leap year, right? And now you got it in front of you once, you can copy it out and paste it into a handy place for future use via copy and paste. It's a quick and simple test for anything in the reasonable realm folks are likely to work in, though in 2020, that not-a-leap-year in 2000 is probably usually no a concern.