Re arrange Date in Column from yyyymmdd to mmddyyyy

Topic

New Reply

I have a file with 2000 entries for demographics. Unfortunately the date of birth column shows dates as yyyymmdd instead of mmddyyyy And to make things worse excel is recognizing entries as “general” and not dates, so when I try to use a date formula I get ####### in the field
Is there a simple date formula to change this or do I have to parse things and then combine columns. This has to work on a 2000 plus rows for the spreadsheet
Any help appreciated
Thanks
JRK
example 19470302 should read march 2 1947 or 03/02/1947

Reply | Quote

Replies

Hi

..you could use code like this:

Code:

Sub convertDates()

Application.ScreenUpdating = False

For Each cell In [d2:d2001] '<< adjust range required
zYear = Left(cell, 4)
zMonth = Mid(cell, 5, 2)
zDay = Right(cell, 2)
zDate = DateSerial(zYear, zMonth, zDay)
cell.Value = zDate
cell.NumberFormat = "dd-mmm-yyyy"   '<< adjust to suit
Next

End Sub

..see attached example file, with 2000 datevalues

zeddy

Reply | Quote

The non-macro formula would be: =DATE(LEFT(D2,4),MID(D2,5,2),RIGHT(D2,2)) and then fill down and format the date column as you want. In the short macro from Zeddy, you can see that used in the zYear, zMonth and zDay lines.

Reply | Quote

Dear Zeddy and Kweaver both of your solutions are phenomenal. Thankyou. I ran both and because some cells within the 5000 Date of birth records have 0 for a number because the date of birth had not been recorded both solutions error out. Is there a way to build in error handling for records that have 0 or are blank in their cells. I would manually enter but it seems that there are approx. 100 records throughout the spreadsheet….
Thank
Jrk

Reply | Quote

JR,

Here’s how to fix both:

Code:

Sub convertDates()

   Application.ScreenUpdating = False

   For Each cell In [d2:d2001] '<< adjust range required
       If cell.Value  "" And cell.Value  0 Then
         zYear = Left(cell, 4)
         zMonth = Mid(cell, 5, 2)
         zDay = Right(cell, 2)
         zDate = DateSerial(zYear, zMonth, zDay)
        cell.Value = zDate
        cell.NumberFormat = "dd-mmm-yyyy"   '<< adjust to suit
     End If

   Next

End Sub

Formula: [noparse]=IFERROR(DATE(LEFT(D2,4),MID(D2,5,2),RIGHT(D2,2)),"")[/noparse]

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

always says false in cell for the formula version , whether dob cell has real value or 0
I did not check the macro version as I have to plug it into the database sheet and change reference values….

Reply | Quote

Hi I am probably messing it up but I just don’t see it..

Reply | Quote

JR,

Seems to work for me am I missing something?
39996-dateconv
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi

try this..

Code:

Sub convertDates()

Application.ScreenUpdating = False

For Each cell In [d2:d2001] '<< adjust range required
If cell Like "########" Then
zYear = Left(cell, 4)
zMonth = Mid(cell, 5, 2)
zDay = Right(cell, 2)
zDate = DateSerial(zYear, zMonth, zDay)
cell.Value = zDate
cell.NumberFormat = "dd-mmm-yyyy"   '<< adjust to suit
End If
Next

End Sub

This makes sure that only cells that have 8 digits are processed.

see also attached..

zeddy

Reply | Quote

Zeddy,

Cool! :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi just got the file to upload so you can see it… Thx
ALSO FAILS WITH DOB IN THE YEAR 1899 HAS TO BE 1900 AND LATER FOR THE MACRO VERSION

Reply | Quote

Hi

..you have four formulas in cell [O2], you should only have one!!

RG wasn’t missing anything at all!
..but using xxx Like “########” will trap for other invalid cell contents e.g 194712, 1947121326, etc
see attached file with fix

zeddy

Reply | Quote

Hi

As far as dates prior to 1900, are they still alive?
Can’t you put them onto another tab??

or amend code to use
.. Like “19######”
which would skip these entries like 18991207 etc etc

zeddy

Reply | Quote

Many thanks, just perfect
phenomenal all works and I changed the 1800 dob pts to 1900 just legacy patients in the database
Thanks again
JK

Reply | Quote

I use dates prior to 1900 because of my genealogy album I’m building
I’m certainly no expert like these other fellows are but this is what works for me

How to calculate ages before 1/1/1900 in Excel http://support.microsoft.com/kb/245104
— Check out that website

This is what works for me but I have lost track of how it works, but it does in my case

Start Excel. View the worksheet on which you want to use the function.
Press ALT+F11 to switch to the Visual Basic Editor.
On the Insert menu, click Module.
Type the following code in the module

‘ This is the initial function. It takes in a start date and an end date.
Public Function AgeFunc(stdate As Variant, endate As Variant)

‘ Dim our variables.
Dim stvar As String
Dim stmon As String
Dim stday As String
Dim styr As String
Dim endvar As String
Dim endmon As String
Dim endday As String
Dim endyr As String
Dim stmonf As Integer
Dim stdayf As Integer
Dim styrf As Integer
Dim endmonf As Integer
Dim enddayf As Integer
Dim endyrf As Integer
Dim years As Integer

‘ This variable will be used to modify string length.
Dim fx As Integer
fx = 0

‘ Calls custom function sfunc which runs the Search worksheet function
‘ and returns the results.
‘ Searches for the first “/” sign in the start date.
stvar = sfunc(“/”, stdate)

‘ Parse the month and day from the start date.
stmon = Left(stdate, sfunc(“/”, stdate) – 1)
stday = Mid(stdate, stvar + 1, sfunc(“/”, stdate, sfunc(“/”, stdate) + 1) – stvar – 1)

‘ Check the length of the day and month strings and modify the string
‘ length variable.
If Len(stday) = 1 Then fx = fx + 1
If Len(stmon) = 2 Then fx = fx + 1

‘ Parse the year, using information from the string length variable.
styr = Right(stdate, Len(stdate) – (sfunc(“/”, stdate) + 1) – stvar + fx)

‘ Change the text values we obtained to integers for calculation
‘ purposes.
stmonf = CInt(stmon)
stdayf = CInt(stday)
styrf = CInt(styr)

‘ Check for valid date entries.
If stmonf 12 Or stdayf 31 Or styrf < 1 Then
AgeFunc = "Invalid Date"
Exit Function
End If

' Reset the string length variable.
fx = 0

' Parse the first "/" sign from the end date.
endvar = sfunc("/", endate)

' Parse the month and day from the end date.
endmon = Left(endate, sfunc("/", endate) – 1)
endday = Mid(endate, endvar + 1, sfunc("/", endate, sfunc("/", endate) + 1) – endvar – 1)

' Check the length of the day and month strings and modify the string
' length variable.
If Len(endday) = 1 Then fx = fx + 1
If Len(endmon) = 2 Then fx = fx + 1

' Parse the year, using information from the string length variable.
endyr = Right(endate, Len(endate) – (sfunc("/", endate) + 1) – endvar + fx)

' Change the text values we obtained to integers for calculation
' purposes.
endmonf = CInt(endmon)
enddayf = CInt(endday)
endyrf = CInt(endyr)

' Check for valid date entries.
If endmonf 12 Or enddayf 31 Or endyrf endmonf Then
years = years – 1
End If

If stmonf = endmonf And stdayf > enddayf Then
years = years – 1
End If

‘ Make sure that we are not returning a negative number and, if not,
‘ return the years.
If years < 0 Then
AgeFunc = "Invalid Date"
Else
AgeFunc = years
End If

End Function

' This is a second function that the first will call.
' It runs the Search worksheet function with arguments passed from AgeFunc.
' It is used so that the code is easier to read.
Public Function sfunc(x As Variant, y As Variant, Optional z As Variant)
sfunc = Application.WorksheetFunction.Search(x, y, z)
End Function

Save the file.
Type the following data
A1 01/01/1887
A2 02/02/1945

In cell A3, enter the following formula
=AgeFunc(startdate,enddate)
where startdate is a cell reference to your first date (A1) and enddate is a cell reference to your second date (A2).

The result should be 58
— I have to admit I do not know what this "The result should be 58" references

HP EliteBook 8540w laptop Windows 10 Pro (x64)

Reply | Quote

Hi cmptrgy

Thanks for explaining what dates prior to 1900 are used for.
Re: The result should be 58″, << this is the result of [A2] – [A1] i.e. the age of the person if [A1] = birthdate and [A2] = when they went through the pearly gates.

zeddy

Reply | Quote

JR,

You can fix the formula version as follows:
[noparse]=IF(INT(LEFT(N2,4))<1900,"Prior 1900",IFERROR(DATE(LEFT(N2,4),MID(N2,5,2),RIGHT(N2,2)), ""))[/noparse]
40001-dateconv
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

So I see this forum has moved to Los Angeles?
Is it warmer than New York there?
Haven’t been to LA for a while.

zeddy

Reply | Quote

So I see this forum has moved to Los Angeles?
Is it warmer than New York there?
Haven’t been to LA for a while.
zeddy

Zeddy,

Yes LA is warmer than NY but what do you mean by the forum moving there? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

Your post#15

the forumla version

:rolleyes::):cool:

zeddy
(I can do smileys now!)

Reply | Quote

many thanks, beyond phenomal JR

Reply | Quote

Unusually warm in San Diego today with highs in the mid- to high-80s.
We are in spring…LOL…when it doesn’t rain for the balance of the year adding to our multi-year drought.

New York’s in the 40s.

Reply | Quote

that sounds like the Depression.
too cold for me, rather be in La Jolly

zeddy

Reply | Quote

It’s Paradise here in The Jewel. Our SUN TAX is put to good use. We just need RAIN!

Reply | Quote

Zeddy,

Yeah! My fingers are faster (or slower) than the grey matter! :cheers:

FYI: I fixed it.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

To convert 19470302 in cell D3 to 3/2/1947 in the same cell D3, you can use the following code:

Code:

Sub convertDates()
    Range(“D3”) = Format(Range(“D3”), “####-##-##”)
    Range(“D3”) = Format(Range(“D3”), “mm/dd/yyyy”)
End Sub

HTH,
Maud

Reply | Quote

Maud,

Nice work! :clapping: But for those of us who hate to type:

Code:

Sub convertDates()
    Range("D3") = Format(Format(Range("D3"), "####-##-##"), "mm/dd/yyyy")
End Sub

Also more efficient in large workbooks as it saves a reference to the cell for each occurrence.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi
Maud: top-marks for a shorter method!

RG: for even less typing:

Code:

[d3] = Format(Format([d3], "####-##-##"), "mm/dd/yyyy")

But, you should be aware that this will NOT actually display the specified “mm/dd/yyyy” format in the cell if you are in the UK. (It will display as UK format dd/mm/yyyy, even though you have asked for “mm/dd/yyyy”)

RG: Try this (or anyone if you are in the USA):

Code:

Sub convertDates()
    Range("D3") = Format(Format(Range("D3"), "####-##-##"), "dd/mm/yyyy")
End Sub

Did you still get your mm/dd/yyyy US format, even though you asked for dd/mm/yyyy ????

To make sure that you get the format you actually specify, you need to specifically use..
cell.NumberFormat = “mm/dd/yyyy”

So my amended routine, taking account of post#11, post#13, post#24 is now:

Code:

Sub convertDates()
Application.ScreenUpdating = False
For Each cell In [d2:d2001] '<< adjust range required
If cell Like "19######" Then
cell.Value = Format(cell, "####-##-##")   '<< converts to date
cell.NumberFormat = "mm/dd/yyyy"   '<< adjust format to suit
End If
Next
End Sub

Thanks again to both Maud and RG for their versions.

zeddy

Reply | Quote

Zeddy,

It looks like a YMMV situation!
40009-zeddydate
Cell D3 starting value: 19491115 (Nov 15, 1949)
:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

What’s a YMMV situation?? (you got me back there :huh: )

40016-pic1

As you can see, I don’t get the mm/dd/yyyy as asked for.

zeddy

Reply | Quote

Zeddy,

Your Mileage May Vary! This is the disclaimer they always give in car ads when they mention the gas mileage the car is supposed to get according to our EPA (environmental protection agency).

Trying to make sure things work across international boundaries is always a pain!

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Trying to make sure things work across international boundaries is always a pain!

..are you talking about North Carolina?

zeddy

Reply | Quote

zeddy thanks for that explanation
You are correct, I have an excel spreadsheet for that information and I’ve been using it so naturally I couldn’t relate to it other than it works

HP EliteBook 8540w laptop Windows 10 Pro (x64)

Reply | Quote