Search code examples
excel

Return the 30th day previous month in excel

I want to return the 30th of each day for the previous month in excel. I used:

EOMONTH(A1,-1)

But that will always return the last day of the prevois month. How could you do it so for example the 30th June will return the 30th May NOT 31st May which is returned by the 'EOMONTH' function.

Thanks

Solution

  • You could perhaps use an IF() along with a TEXT() to do that.

    =IF(TEXT(EOMONTH(A1,-1),"dd")="31",EOMONTH(A1,-1)-1,EOMONTH(A41,-1))

    If the date of EOMONTH(A1,-1) is 31, then take one less day, otherwise, take that date itself.