Date Variation Problems

[jamesdmurray]

I apologize if this has been answered elsewhere -- I've done a ton of Google searching and can't find anything similar.

My problem:

I have a list of documents that I'm sorting in Excel. Each entry contains a name and a date (and some other related data).

Unfortunately, some of my dates are incomplete. Most are DD/MM/YYYY, but some are only MM/YYYY and others are only YYYY.

How do I enter the MM/YYYY and YYYY so that they can be sorted by date in the same column as DD/MM/YYYY?

As a bit of supplementary information:

I'm importing a directory full of PDF files with specifically formatted file names. I'm importing the directory structure into Excel using a macro, then using "Text to Columns" to split into multiple columns. But since some of the PDF files don't have a day or month, the date column doesn't format correctly.

[MarvinP]

Hi james and welcome to the forum

If you supply small sample of your data, we could (most likely) solve your problem. You might simply just want to supply the Date Column as it comes into your file. Then if it is year only, did you want Jan 1 or Jun 1 or some other day to get appended? If it is Month and Year, did you want the first day of the month or the 15th of the month to be appended to the date?

Click on Go Advanced, under the message area and then on the PaperClip Icon that will appear above the message area to attach/upload a file for us to examine.

[jamesdmurray]

Thank you for the response! I've attached a sample as requested. You'll notice that one of the dates is simply listed as "Unknown." This date sorts correctly, since it just gets kicked to the end.

Ideally, an incomplete date would look like the following:

XX/XX/2005

or

XX/06/2005

Instead of XXs, any other null symbol could be used, including 00s.

When they sort, it'd be nice if the XX/XX/2005 was read as 01/01/2005 and the XX/06/2005 was read as 01/06/2005.

So in other words, in an ideal office world, the cell would DISPLAY XX/XX/2005, but would be READ by the program as 01/01/2005.

ANY other suggestions would be greatly appreciated

Again, thank you so much for your help.

[MarvinP]

HI James,

Find the attached where there is an added column which converts Unknown to 1/1/200 and converts those XX to 01 and returns a DATE. Sorry the formula is long. If you want to keep both columns that would allow you to sort on the XX or by Date in the Date2 column.

Hope this helps - see attached

[jamesdmurray]

Thank you very, very much for your great answer and speedy turnaround!

[LauraBH1]

Can you tell me what formula would be used if some of the data is just a four digit year (not xx-xx-1990, as in the example above, but just 1990) and I want those cells to be changes from 1990 to 1/1/1990? Problem is that some cells in the column have the full mm/dd/yy and others have yyyy. I tried the formula above, but it didn;t work on my data, probably due to the variation in my data. Appreciate any help. I am really stuck. thanks!