Creating a changeable value in EDATE formula

[simmo86]

I am trying to have the EDATE formula change based on today's date.

I am using the following formula:

=TEXT(AX114,"mm")-TEXT(TODAY(),"mm")

Where AX114 is a date (01/05/2015). If I break apart the two TEXT formulae, they produce a 5 and a 6. Therefore the result should be -1.

Is there anyway of re-writing the formula above in order to produce a value of -1?

Many thanks,

Alex

[judgeh59]

will this work for you?

Please Login or Register  to view this content.

EDIT: this assume that your date is a real XL date. IE: a number

[samba_ravi]

if the not works attach sample file with expected result

[simmo86]

Thank you so much! That works perfectly except when it goes into a previous year. i.e. the result when it goes back to:

February it gives 2-6 = -4
January it gives 1-6 = -5

But then December it gives 12-6 = 6

Rather than -6.

Can you think of a solution to keep it going back further and further? Maybe I have to include years aswell?

Many thanks,

Alex

[daddylonglegs]

Try using this formula

=DATEDIF(AX114,TODAY(),"m")*-1

[Pepe Le Mokko]

Perhaps =mod(MONTH(A1)-MONTH(TODAY(),12)-12 ( works but not elegant)

[simmo86]

neither of those formulae seem to work. The DATEIF formula produces a whole series of hash tags and the second formula says there are too many arguments?

[judgeh59]

I think there should be a ")" after the TODAY() ....I adjusted it like that and it worked really slick....

Please Login or Register  to view this content.

[Pepe Le Mokko]

Thanks judge

[judgeh59]

my pleasure...

[judgeh59]

@simmo86 --- so did this fix your issue?...

[daddylonglegs]

neither of those formulae seem to work.

It would be helpful if you were to define exactly what results you expect and what the parameters are.

Is AX114 always a date in the past?

How far in the past might it be?

Is it always 1st of the month or can it be any date?

Depending on the answers to the above it would then be helpful to give some sample dates with expected results. For example, we know you want -6 if AX114 is a December 2014 date (based on today being in June 2015). Is the result the same for any date in the month, what if the date is December 2013 or December 2000, what result do you expect then?