End of month (either current or prior month)

[bunfuns]

Hi all,

I want to write a formula that returning either the end of month of this month or the prior month.

If a date is already at end of month, then I'll take the date as is. Example: 1/31/14 should return 1/31/14.

If a date is before end of month, I want Excel to give me the end of month from the prior month. Example: 1/15/14 should return 12/31/13.

Was trying to use EOMONTH, but didn't know how to test if a date is already at the end of month.

Thanks,
Susana

 

[JoeMo]

If your date is in A1 then in B1:
=IF(A1=EOMONTH(A1,0),A1,EOMONTH(A1,-1))

 

[Snakehips]

Susana,

Maybe....

Excel 2007
	A	B
1	31/01/2014	31/01/2014
2	15/01/2014	31/12/2013
Sheet1

Cell Formulas
Range	Formula
B1	=IF(EOMONTH(A1,0)=A1,A1,EOMONTH(A1,-1))

Hope that helps.

 

[XOR LX]

Or:

=MIN(EOMONTH(A1+1,{0,-1}))

Regards

 

[bunfuns]

Works perfectly!! Thanks for the quick respond!!!