Converting Values to a date

Lucas in London

Board Regular
Joined
Jun 17, 2002
Messages
88
Hi,

I have a spreadsheet I download that has dates stored in a non date format and I'm struggling to find an easy way to convert it to a format I can use.

The data is monthly and the dates are stored in the format: YYYY.MM - as shown for a sample of dates below:

2008.01
2008.02
2008.03
2008.04
2008.05
2008.06
2008.07
2008.08
2008.09
2008.1
2008.11
2008.12

I would like to conver these into the format: MMM-YY so for instance 2008.12 becomes Dec-2008 etc. I would usually use something like the text function (Text (value, "MMM-YY")) but this does not work has these entries are not recognised as dates! Anyone have any alternative ideas? The dates are in column A.

Thanks,

Lucas
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
I think with a formula you can do it like this:

If your value is in A1, this Formula is in B1:

=DATE(LEFT(A1,4),SUBSTITUTE(RIGHT(A1,2),".","")+1,0)

The idea is to strip out the period and then convert to a date with the year and month. But we assume we want the last day of the month -- as your dates don't have a day. is that okay? You can format the date as you like.

------------------------------------------------------
I suppose you could also get the text value like this too:

=TEXT(DATE(LEFT(A1,4),SUBSTITUTE(RIGHT(A1,2),".","")+1,0),"mmm-yy") EDIT: Duh...You mentioned that already in your post... :)

Does that work?
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,562
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
either of the following should work
format to your preference


=(RIGHT(A6,2)&"/"&LEFT(A6,4))+0
=DATE(LEFT(A6,4),MID(A6,6,2),1)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,573
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top