partial date to useable date in Excel

Does anyone know a formula or macro that will check a cell in excel, and if it finds a full date return the full date, if there is just a year, it will convert it to 1/1/yyyy

Such that:

datefix(2/4/2000) = 2/4/2000
datefix(2000) = 1/1/2000
JAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Add this User Defined Function to a module.

Function DateFix(c As Range) As String

If Len(c) > 4 Then
    DateFix = CDate(c)
Else
    DateFix = c
End If
End Function

Open in new window


Just in case you need them, here are instructions on how to install the UDF.

In Excel, Press Alt+F11 to open the Visual Basic Editor (VBE)

Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu

Copy the UDF and paste it into the right-hand pane of the VBA editor ("Module1" window)

Press Alt+F11 again to go back to Excel
0
Danny ChildIT ManagerCommented:
Ok, unless I'm missing something, this is trickier than it looks.  I'm not sure what date format you use, whether it's dd/mm/yyyy or mm/dd/yyyy, but I'm going by the former due to the time you posted it being more in line with Europe than the US.

In summary, if there's only 4 digits between the  ( ), you want the date to come up as 1/1/yyyy
but if not, extract the actual values in the ( )

The tricky bit is it's not clear whether your days and months are listed as single digits, or two - and also how to cope with finding the MID values when a date could be 3/5/2000 or 23/12/2000

To get around this, I used helper cells to find the position of the first ( and the first / and the second /
This lets me figure out how many characters to extract.

The attached sheet seems to work, it's just a bit long-winded.
ee-datefix.xlsx
0
Danny ChildIT ManagerCommented:
or, write a macro to do a series of Find/Replaces, based on length.
If it's a short date, F/R "datefix(" with "1/1/"
if it's a long date, F/R "datefix(" with ""
F/R ")" with ""
 - you might also need a copy/paste special Values over the top.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Martin LissOlder than dirtCommented:
My UDF was incorrect.

Function DateFix(c As Range) As String

If Len(c) > 4 Then
    DateFix = CDate(c)
Else
    DateFix = CDate("01-01-" & c)
End If
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Martin

Your UDF will return a text string so the date returned by the UDF will look like a date but actually be a text string and so ideally it won't be correct while dealing with the dates.
So what if we return the UDF output as Date, of course OP will need to format the formula cells as Date.
What is your opinion?

Function DateFix(Cell As Range) As Date
If IsDate(Cell) Then
    DateFix = CDate(Cell)
Else
    DateFix = CDate("01-01-" & Cell)
End If
End Function

Open in new window

0
Martin LissOlder than dirtCommented:
Your UDF will return a text string so the date returned by the UDF will look like a date but actually be a text string and so ideally it won't be correct while dealing with the dates.
Good point.

So what if we return the UDF output as Date, of course OP will need to format the formula cells as Date.
Yes, that works.
1
Martin LissOlder than dirtCommented:
@D

I'm a little embarrassed to have been given all the points. I'd be happy if you Request Attention so that you can have a moderator reopen the question so that you can share the points with at least sktneer.

In any case I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
0
JAuthor Commented:
Martin,

I'm confused a little as it seems to work if I do =datefix(G100) where G100 has for example 1957, it returns 01/01/1957 and if I do =datefix(G100) where G100 has '04/11/1928' then it returns 04/11/1928 so it works?
0
Martin LissOlder than dirtCommented:
If you are happy, I'm happy.
0
JAuthor Commented:
Martin,
What am I missing here, you are not happy with the solution, I'm curious as to why.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Martin

Thanks for considering the point. And no worries for points. You well deserve them.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@D
Excel treats Dates as numbers. The problem is the UDF will return the string not the actual number though it appears like a date in the cell.
To check that, assuming you have =datefix(G100) in H100, then in any cell place the formula =ISNUMBER(H100) and it will return False. That means you have values in the formula cells which look like Dates but not the actual dates as per the excel database.

Hope this helps.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.