DATE

[annadinesh]

Dear Team

I want a formula in a cell which shows

I have a formula in cell K4 is =IF(E4>=DATE(2018,2,1),E4+1095,"") which shows the date after 3 years of E4 in K4

date in cell E4 is 01.12.2018

but I want

in K4 that if the difference of the date <1095 the k4 should blank


Thanks and regards


Dinesh Saha
9932022569

 

[Joe4]

What is the format of cell E4?
Is cell E4 a valid date with special formatting, or is it a text entry?

If you are unsure, enter this formula in any blank cell and tell us what it returns:
=ISNUMBER(E4)
If it returns FALSE, you have a text entry.

Also, in your date 01.12.2018, is that mm.dd.yyyy or dd.mm.yyyy?

 

[annadinesh]

What is the format of cell E4?
Is cell E4 a valid date with special formatting, or is it a text entry?

If you are unsure, enter this formula in any blank cell and tell us what it returns:
=ISNUMBER(E4)
If it returns FALSE, you have a text entry.

Also, in your date 01.12.2018, is that mm.dd.yyyy or dd.mm.yyyy?

manual date entry DATE on cell E4
dd.mm.yyyy

 

[Phuoc]

Try this:

=IF(DATE(RIGHT(E4,4),MID(E4,3,2),LEFT(E4,2))>1,DATE(RIGHT(E4,4),MID(E4,3,2),LEFT(E4,2))+1095,"")

 

[Joe4]

manual date entry DATE on cell E4
dd.mm.yyyy

You did not answer my question regarding what this formula returns:
=ISNUMBER(E4)

Also note that adding 1095 might not always work properly if a leap year is in the middle of there (you will be off one day).
Better to use EDATE, in which you can add an exact number of months, i.e.
=EDATE(E4,36)