Dates aren't formatting. Some change to strange dates like 2/27/8093 & some ###'s

**dawnh44** · 11-26-2013, 09:39 AM

Hello everyone,

Does anyone have a work around to help me format my dates in a column? The following dates will not format correctly:
2091989
2091989
2091989
8031971
8031971
8031971
3301987
3301987
3301987

When I format them they change to

9/1/7627
9/1/7627
9/1/7627
######
######
######

I've tried text to columns, but I'm still unable to format them correctly. Does anyone have any ideas?

Thank you.

**ragulduy** · 11-26-2013, 10:13 AM

=DATE(RIGHT(A1,4),LEFT(A1,IF(LEN(A1)=7,1,2)),MID(A1,IF(LEN(A1)=7,2,3),2))
I think that would return the date value for you, which you can then format as a date

**dawnh44** · 11-26-2013, 11:04 AM

yudlugar...You are the best. Thank you, thank you, thank you. I've been working on looking for an answer for this for a while.

Dates aren't formatting. Some change to strange dates like 2/27/8093 & some ###'s

LinkBack

Thread Tools

Rate This Thread

Display

Dates aren't formatting. Some change to strange dates like 2/27/8093 & some ###'s

Re: Dates aren't formatting. Some change to strange dates like 2/27/8093 & some ###'s

Re: Dates aren't formatting. Some change to strange dates like 2/27/8093 & some ###'s

Thread Information

Users Browsing this Thread

Similar Threads

Conditional Formatting, Cells containing dates. How do I have dates and text?

[SOLVED] Conditional Formatting of: dates within 1 month and dates before today.

Use of a Drop Down Box to Select Items and Change Dates and Conditional Formatting

Change 2010 Dates to 2008 Dates

[SOLVED] Why aren't dates always available to group in pivot tables?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions