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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
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
5,195
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,670
Messages
5,838,688
Members
430,563
Latest member
Raeyven

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