Convert YYYYMMDD to Valid Date in Excel

[gahill]

Hello:
I have attempted to write a function in Excel 2000 to convert a date coming from a legacy system in YYYYMMDD format to a valid date using the mid function with no success.
Of course I can do this in a cell and copy the formula to the end of the region and then convert formula's to values.
However I have many instances where a function would be perfect. Any help would be greatly appreciated.
Thank you
Gary W. Hill

 

[SkipVought]

Hi,

Why do Dates and Times seem to be so much trouble? faq68-5827

Using the MID function creates a STRING and not a DATE.

Use the MID function in the DateSerial function, instead.
[tt]
s = "20050505"
MsgBox DateSerial(Left(s,4), mid(s,5,2), right(s,2))
[/tt]


Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[gahill]

Hi Skip:
My terminology was wrong in my post.
The date I'm attempting to display in Excel is what a user is used to seeing 08/09/2005.
The people getting the result spreadsheet are not going to use the date for any type of calculations.
I just want the result for display purposes.
I am making customer usage reports and they always complain if I forget to display the date as they are used to seeing.
Thanks
Gary

 

[SkipVought]

So you do NOT want to...

Convert YYYYMMDD to Valid Date in Excel

So exactly what IS the issue that you can't...

using the mid function

Please post the code for the FUNCTION that you mentioned in your original post.



Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[gahill]

Hi Skip:
Problem solved. I had a temporary memory lapse that you solved when you pointed out a string was being returned.
I fixed the function and it works fine now.
Thank you for your prompt response it was greatly appreciated.
Sometimes during a hectic day you cannot see the forest for the trees.
Gary