Add a Fraction year to Date

[Iceshade]

Hi All,

Is there a way to add a fraction years/months to a date?

E.g. H1 = "03/08/2021" + J1 which is "8.75" (where 8.75 is 8 years and 9 months) to give me a new date of "03/05/2029"

Would greatly appreciate any help on this !

Thank you

 

[Gimics]

Hey Iceshade - adding to a date will increase by days at first (you should see this behaviour if you checkout what you're suggesting in an excel spreadsheet).

March 3, 2021 + 8.75 = March 16, 2021.

Also, just to be clear, March 8 2021 + 8.75 years does not equal March 5, 2029.

It seems like you're actually working with months, so your better bet would be to use the =edate() formula, which allows you to add months to your original date.

K1: =EDATE(H1,J1*12)

which will give you December 8, 2029 (which is the correct result).

 

[Joe4]

Yep. Use this formula:

=EDATE(H1,J1*12)

 

[Joe4]

Gimics,

I think they are using a European date format, so 03/08/21 is really August 3, 2021 and 03/05/2029 is really May 3, 2029.

Also, in your formula, "x" is not multiplication in Excel, "*" is.
So it needs to be:

J1*12

not

J1x12

 

[Gimics]

Classic!

 

[Iceshade]

Hey Iceshade - adding to a date will increase by days at first (you should see this behaviour if you checkout what you're suggesting in an excel spreadsheet).

March 3, 2021 + 8.75 = March 16, 2021.

Also, just to be clear, March 8 2021 + 8.75 years does not equal March 5, 2029.

It seems like you're actually working with months, so your better bet would be to use the =edate() formula, which allows you to add months to your original date.

K1: =EDATE(H1,J1*12)

which will give you December 8, 2029 (which is the correct result).

Haha sorry, I am in Australia so my date format is ddmmyy so my dates read August 3rd 2021 to May 3rd 2029

I'll give this a try - thank you

 

[Iceshade]

Hey Iceshade - adding to a date will increase by days at first (you should see this behaviour if you checkout what you're suggesting in an excel spreadsheet).

March 3, 2021 + 8.75 = March 16, 2021.

Also, just to be clear, March 8 2021 + 8.75 years does not equal March 5, 2029.

It seems like you're actually working with months, so your better bet would be to use the =edate() formula, which allows you to add months to your original date.

K1: =EDATE(H1,J1*12)

which will give you December 8, 2029 (which is the correct result).

Thanks @Gimics this seems to have done the job. Greatly appreciated. One last thing, not sure if you can do anything about it however - is there a way to show a blank in Column K1 if there is no date in Column H1 ? - It gives me a 0/01/1900 date on blanks

 

[Gimics]

For sure - you could just use a simple IF formula if you'd like:

K1: =IF(ISBLANK(H1),"",EDATE(H1,J1*12))

 

[Iceshade]

For sure - you could just use a simple IF formula if you'd like:

K1: =IF(ISBLANK(H1),"",EDATE(H1,J1*12))

Perfection ! I haven't touched Excel in so long I really need to keep myself active with it - Can't thank you enough, greatly appreciated.