Formatting Date

iksotof
3StarLounger
Posts: 313
Joined: 04 May 2010, 15:18

Formatting Date

Post by iksotof »

Hi

I ahve received a lsit with lots of dates in this format, they are as gneeral text,

Aug'20
Jul'21
Apr'19
Aug'22
Aug'22


What is the best way to have them as date strings so i can some anlaysis on them? Happy to have a default of 1st as the day on all of them.


Thank you

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formatting Date

Post by HansV »

Let's say you have such values in A2 and down.
In another cell in row 2, enter the formula

=DATEVALUE("1-"&SUBSTITUTE(A2,"'","-"))

Format the cell with the formula as a date, then fill down.
Best wishes,
Hans

iksotof
3StarLounger
Posts: 313
Joined: 04 May 2010, 15:18

Re: Formatting Date

Post by iksotof »

thank you Hans, super. Better than my text to cooumns then concatanating ha.