Reverse date

[hlkelly]

Is there a way to force Excel to change a date that reads: 112961 to 1961/11/29 ?

I can seem to get it to flip the numbers around properly. Any suggestions? It does not matter if the cell is actually formatted as a date. I just need the number to read in reverse.

 

[AnotherHiggins]

Well it kind of does matter if the cell is formatted as date. Because if it is, then all you have to do is go into Format > Cells, choose custom on the left and type in "yyyy/mm/dd" (or any other format you want).

But since 112961 probably is not currently formatted as date, you need to do a little manipulation.

[COLOR=blue white]=date(Right(A1,2),Left(A1,2),Mid(A1,3,2))[/color]
Will return the cell as a date. Now just change the formatting.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[hlkelly]

The cell is currently formatted as a date, however, typing in yyyy/mm/dd does NOT return the date in that format.

I tried the formula you provided but that did not work either. It changes the date/number completely.

The cells are currently formatted as "general".

 

[SkipVought]

Hi,

You need to understand MS Dates.

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

Skip,

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

 

[AnotherHiggins]

OK - There is a difference between being stored as a number and being stored as a date. For example, today is 8/25/2005. If you type that into an excel cell, it will display in a way that you recognize as being a date. But if you format that cell as General, you will see that the 'serial number' that Excel stores for 8/25/05 is actually 38589. That's 38,589 days since 1/1/1900. For more info on how Excel deals with dates and times, see SkipVought's faq, Why do Dates and Times seem to be so much trouble?.

All of that having been said, if '112961' was indeed formatted as a date, then going into format > cells on the Number tab would reveal that the cell is not formatted as 'General', but as 'Custom', and in the box on the right it would say 'mmddyy'.

Make sure to change the A1 in the formula I provided to whatever cell contains 112961.

Also, is your format always 6 digits? What if the month is January and the day is the fifth in the year 1961? Would have 010561 or 1561?

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[hlkelly]

I suppose this will further complicate my issue:

Cell A1 contains the following: 82205
Cell A1 is formatted as "general"

The data has been exported from SPSS (statistics software if you're familiar) into Excel.

I need 82205 to appear as 2005/08/22.

I tried =DATEVALUE(a1) but I get an error message #VALUE!

I am thinking if I can get A1 to reveal the serial number behind it, I can get it back into a date format and then change the cell to a United Kingdom date format.

I know I'm missing something.

 

[AnotherHiggins]

=>Cell A1 contains the following: 82205

I'm afraid that does complicate things because now we know that the number of characters can vary.

We'll need to see an example of every type of entry you'll run across. That will help develop a comprehensive formula that will always work for you.

Please provide actual examples of SPSS's output of the following data types:
[tt]1) 2 digit month, 2 digit day (like Nov 29)[/tt][blue]<Your orig example[/blue]
[tt]2) 1 digit month, 2 digit day (like Jan 29)
3) 2 digit month, 1 digit day (like Nov 1)
4) 1 digit month, 1 digit day (like Jan 1)[/tt]

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[hlkelly]

I figured out a solution myself:

I selected cell A1 containing 82205
Performed a text to columns (delimited, tab, TEXT)
Performed ANOTHER text to columns (delimited,tab, DATE)
Then, formatted cell A1 as date, English-United Kingdom and selected the format yyyy/mm/dd.

Now I'll write a macro that does exactly this to each exported file and I should be good to go.

Do you forsee any problems with my solution?

Thanks for your time and assistance John!!!

Heather