Excel Date Formatting Problem

[sandybam]

Hi Everyone,

I have a spreadsheet that houses survey results pulled from our Web Page but the date field for each record is showing as 2.00211E+13. However when you click into the cell the true value is shown in the formula bar as 20021116121646. But I cannot see this true value unless I click into each cell. I would like to format these values to look like the following:

2000/11/16 12:16:46

Unfortunately, the only way I can do it is to click into each date cell and manually reformat the date in the formula bar. I tried to record a macro but the macro captured the values of the cell I used to record on instead of just the keystrokes. I also tried numerous formats but they only gave me the same result of 2.00211E+13 or they would return endless pound signs #################.

Does anyone have any ideas on what I can do to reformat this field?

Thank you,
Sandy

 

[lhite]

When you do your downloads add another column to the sheet to convert the date into a format that Excel will recognize. The following formula should do the trick, assuming that the value is in cell A1:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),RIGHT(A1,2))

Then just copy and paste the formula down the column. If you want to get rid of the poorly-formatted date afterwards you'll need to copy the whole column of redone dates and then PASTE SPECIAL, VALUES to convert the formulas to their calculated values, after which you can discard or even replace the original date column.

-Larry

 

[lhite]

I forgot to mention, you will probably need to set the cells' FORMAT property to something that will display the date and time together, otherwise you might just see the date.

-Larry

 

[sandybam]

Wow Larry thank you for such a quick response. This worked perfectly!

Sandy

 

[combo]

You can display such written data using user defined format:
0000"/"00"/"00" "00":"00":"00
but this will not be recognised as valid date/time.

Alternatively you can convert number into date/time value using formula (assumed the number to convert is in A1 cell):
=DATE(VALUE(MID(A1,1,4)),VALUE(MID(A1,5,2)),VALUE(A1,7,2)))+TIME(VALUE(A1,9,2)),VALUE(MID(A1,11,2)),VALUE(MID(A1,13,2)))
This can be used and formatted as date/time in a standard way.

I would create user defined function to convert number this way if I used conversion permanently.