Last Date of the Month as DD-MM-YYYY

[spycein]

Hi Everyone,

I wanted to know what is the formula to convert "MMM-YY" format date to "DD-MM-YYYY" format.

for example,

In cell A1 i have a value May-16, now in cell "B1" i need the value as 31/05/2016.
similary June-16 as 30/06/2016.

The date should last day of the month.

Please help me to solve this.

Thank you so much in advance.

 

[wideboydixon]

On the assumption that A1 is actually a date and not text then B1 formula:

=EOMONTH(A1,0)

How the date is then displayed is down to the cell formatting. Presumably the format in A1 is "mmm-yy" so you need to put format "dd-mm-yyyy" or "dd/mm/yyyy" in to B1.

WBD