Date Format Not Taking

**Mvaldesi** · 07-16-2019, 03:29 PM

Good afternoon, Experts and Gurus!

I’ve got a column of about 32,000 records with dates in a “yyyy-mm-dd” format; I’m trying to get them into the “mm/dd/yy” format, but when I highlight the column and select the latter format from the Format Cells dialogue box, nothing changes. However, if I double-click into one of the cells and then out again, the date snaps into the expected format. Is there a way to fix the format for all 32,000 cells without clicking into each one?

Thanks in advance for any and all help!

**Sam Capricci** · 07-16-2019, 03:43 PM

I tried find and replace. Find what: 1 and Replace with: 1 then replace all and they all changed the way you want them.

**MrShorty** · 07-16-2019, 03:49 PM

As a debug step, I put =ISTEXT(A2) into B2 and copied down. Those that are not formatted correctly are showing as text, where the ones you have already converted are showing as not text. Excel has several strategies for converting numbers/dates stored as text to actual numbers/dates (SamboKid's find and replace strategy is one such strategy). You might review:
https://support.office.com/en-us/art...4-32a67ec0a680
https://support.office.com/en-us/art...1-c5bad0f0a885

**Mvaldesi** · 07-16-2019, 03:51 PM

Wow, that worked like a charm; thank you, Sambo! Might I ask how you knew to do that?

**Sam Capricci** · 07-16-2019, 03:55 PM

If you put =istext(a5) in an empty cell it returned TRUE so excel was seeing them as text.
I've found that simply replacing one number that appears in the cells (like the 1 in this case) prompts them to change to a number and you can get the format you want then.

Date Format Not Taking

LinkBack

Thread Tools

Rate This Thread

Display

Date Format Not Taking

Re: Date Format Not Taking

Re: Date Format Not Taking

Re: Date Format Not Taking

Re: Date Format Not Taking

Thread Information

Users Browsing this Thread

Similar Threads

Taking off items by order of earlier date [Question]

[SOLVED] Subtotal of time, should not taking into account the date

Due Date Calculation by taking only working hours and days

Taking a custom number format to text?

Taking Information only from a specific date

[SOLVED] Excel not taking number format consistently

Date difference taking into account weekends.

Bookmarks

Bookmarks

Posting Permissions