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
Formatting Date
-
- Administrator
- Posts: 78229
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formatting Date
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.
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
Hans
-
- 3StarLounger
- Posts: 313
- Joined: 04 May 2010, 15:18
Re: Formatting Date
thank you Hans, super. Better than my text to cooumns then concatanating ha.