1

I have an excel file and a column of what are meant to be dates in the format of the first 4 digits being the year and the last 2 being the month. Is there a way to format this column in "mm-yyyy" format? I am importing these dates into another system and want to create a date field in the destination system.

Sample column data on the left desired output on the right.

196408 -> 08/1964

196701 -> 01/1967

200102 -> 02/2001

Is there an excel formula or some other regular expression that I could use to do this?

1
1

Assuming dates start in B2 then use the following formula and copy down as needed

=Date(left(B2,4),Right(B2,2),1)

Then apply custom formatting to the cells and set the date format to be mm/yyyy. This method will basically convert your numbered date to a date recognizable by excel and which excel date functions will work with.

Altenative

You could opt to simply treat your date as text which may make it harder to work with down the line if you want to go that route you could use the following formula:

=RIGHT(B2,2)&"/"&LEFT(B2,4)

UPDATE: Blank Cells

To deal with blank cells, check to see if the cell is blank first. If the cell is blank return a blank. If its not blank then perform the operation:

=IF(B2="","",Date(left(B2,4),Right(B2,2),1))
1
  • Not every cell has data unfortunately, but the formula above works as expected. If no one data is in a cell the formula returns #value!. Is there a simple way to add to the above formula to ignore blank cells @forward-ed – west Aug 9 '19 at 3:29
0

You need following formula to convert whole number into specific Date Format.

enter image description here

  • Formula in Cell C38, fill it down.

    =TEXT(DATEVALUE(RIGHT(A38,2)&"/"&MID(A38,6,3)&"/"&LEFT(A38,4)),"dd/yyyy")
    

Adjust cell references in the formula as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.