Date query

[kipaqra]

I am looking for a solution to the following problem.

Cell A1 contains date 18/04/2016

I need to show in Cell A2, the date calculated as 17/05/2016, one month on.

If the date in cell A1 is 01/05/2016, A2 needs to show 30/06/2016.

I have tried extracting the month integer from cell A1 by formula: =month(A1) then adding 1 to it, similar with =day(A1) then rebuilding the date with concatenate function, but when the date in cell A1 is the first of the month, the rebuilt date goes to 00 and not the last day of the previous month.

Please help.

[pjwhitfield]

Please Login or Register  to view this content.

http://www.techonthenet.com/excel/formulas/dateadd.php

[kipaqra]

That just adds a month onto the date in A1 with the same date ie.

If A1 = 18/04/2016, using date add sets A2 = 18/05/2016

Whereas I need the preceding date, the 17th of May.

[samba_ravi]

=edate(a1,1)-1

[kipaqra]

=edate(a1,1)-1

Much appreciated, thank you works fine for middle of the month but still does not work if A1 is 01/04/2016

[pjwhitfield]

Do two DATEADDs then

Please Login or Register  to view this content.

[kipaqra]

Do two DATEADDs then

Please Login or Register  to view this content.

That just returns: #NAME?

[shukla.ankur281190]

Try

=IF(DAY(A1)=1,EOMONTH(A1,-1),EDATE(A1,0)-1)

[kipaqra]

Try

=IF(DAY(A1)=1,EOMONTH(A1,-1),EDATE(A1,0)-1)

That works fine for the 1at of the month but does not work for any other day of the month

[shukla.ankur281190]

As your asked in your post#6 I gave solution accordingly.

If you need anything else please speak inone time pls

[kipaqra]

If I have to use multiple cells to achieve what I want so be i.

[kipaqra]

As your asked in your post#6 I gave solution accordingly.

If you need anything else please speak inone time pls

Did you read the original post?

[shukla.ankur281190]

Can you please upload your spreadsheet here with expected result.

Go to advance click on paper clip icon and attach the workbook