Excel Dates

Montana Man
Montana Man used Ask the Experts™
on
I have a column that displays dates like this:

05/21/1944

I need to have them display like this:

19440521
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
You can use the custom format yyyymmdd for the column.

Note, this will only work if you have 'real' dates, eg not dates stored as text.
Montana ManDigital Leader

Author

Commented:
That works, however i need to double-click in each field to make it change?  Any trick to that?  I have 5000 records..

Thanks!
Steven CarnahanAssistant Vice President\Network Manager

Commented:
If you click the letter at the top of the column (A for example) then it will highlight the entire column and then right click in any cell of that column and change the format.
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Montana ManDigital Leader

Author

Commented:
Tried that but it still makes me double click in each record.
Steven CarnahanAssistant Vice President\Network Manager

Commented:
Did you try pressing F9
Montana ManDigital Leader

Author

Commented:
Just did, i just found another problem with that.  Once the date is converted example:

19440521 i need to take the first 4 digits and combine it into a new field with two other columns like this

Moore         Robert       19440521

Create one column that looks like this:

MooreRobert1944  and the format returns this:

MooreRobert1621

Sorry for being a pain.....
NorieAnalyst Assistant

Commented:
Are you sure Excel is recognising these as 'real' dates?

Sounds like it might not be.

To test you could put this formula in column B and copy down.

=ISNUMBER(A1)

If it returns FALSE the 'dates' aren't being recognised.

To fix that here's a coupe of things you can try.

1 Select the column, goto Data>Text to Columns... and hit Finish.

2 Select an empty cell, copy it, select the 'dates' and goto PasteSpecial and select Add from the Operations section.

Once you've done either of these try the formatting again.
NorieAnalyst Assistant

Commented:
Could you attach a sample workbook?

It appears there's more going on here than just dealing with a column of dates.
Montana ManDigital Leader

Author

Commented:
Can you see my attachment?
Montana ManDigital Leader

Author

Commented:
I'll try it again...
ExpertsHelp.xlsx
Analyst Assistant
Commented:
The dates in column G aren't being recognised as dates by Excel.

Try the Data>Text to columns... again, but instead of hitting Finish move to step 3.

On step 3 select MDY from the Column Data format section.

Then try the custom format I suggested earlier.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial