Date Convertion Formula

[ESTAGroup]

I need to convert a date (e.g. 1/1/2005) to a number 20050101 or convert the other way. Does anyone know a formula that can do either one for me? I do not want to convert to a serial number.

Thank you.

 

[Lilliabeth]

Change 20050101 to 1/1/2005:

Enter this formula in a helper cell:
=MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4)

Select it, hit copy, and then choose Edit Paste Special, and choose Values.

Put a 1 in a cell - out of the way somewhere.

Select and copy the cell with the 1 in it.

Select the cell that used to have the long formula in it and Edit > Paste Special - choose Multiply/

Apply the date format of your choice.

 

[SkipVought]

Hi,

Very simple, IF you have REAL DATES
[tt]
=Text(A1,"yyyymmdd")
[/tt]
Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Lilliabeth]

Hi Skip, If you will notice surferboy's other post in this forum, you'll see why I think the "real dates" is a big "not"!

 

[macropod]

Hi surferboy,

To change the string '1/1/2005' to a string '20050101', you could use:
=TEXT(DATEVALUE(A1),"yyyymmdd")
or
=VALUE(TEXT(DATEVALUE(A1),"yyyymmdd"))
to change it to the equivalent number.

Cheers

 

[SkipVought]

Lilliabeth,

???

Don't understand what you mean

???

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Lilliabeth]

Skip,

http://www.tek-tips.com/viewthread.cfm?qid=1141583&page=2

Formulas formatted as text made me doubt dates were dates.

 

[SkipVought]

Gocha!

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]