assign value to a date?

[clickwhistle]

I am working with a spread sheet that has some dates in it which are unknown, these dates are noted as either ??-???-2010 (dd-mmm-yyyy) for those where only the year is known, or ??-Jan-2010 where the day is not known

Is there a way to assign a value to these odd date formats so they sort correctly? For example, in the case where only the year is known assignment of the values 00-000-yyyy would be helpful.

Anyone have an idea how to do this?

 

[Jonmo1]

Welcome to the board...

I'm sure it's doable, but can you post some sample data of each scenario

 

[clickwhistle]

Literally it would be dates in a column, like this:

22-Jun-1978
25-Jan-1967
??-May-1981
31-Oct-1990
??-???-2010

I'd like for the dates listed as question marks to sort appropriately which would require some value being attached to them (I would think?)

 

[Jonmo1]

OK, so taking that patern quite literally, those are the only scenarios.

If the DAY is unknown, then the day is literally replaced with ??
If the MONTH is unkown, then the month is literally replaced with ???


I would suggest logic like this..

If the Day is unknown, then make it the 1st of whatever Month/Year IS known.
If the Month is unknown, then make it January with the known Day and Year
If both month and day are unknown, them make it January 1 of the known year.

Try

=SUBSTITUTE(SUBSTITUTE(A1,"???","Jan"),"??",1)+0

 

[clickwhistle]

Yeah I was leaning towards that, the only problem is that if there is a known date which actually is the first of the month, etc. Which is what I was trying to avoid

 

[Jonmo1]

Try it...

If the Day IS known, then my formula will use that day.
Only if the Day is UNknown will it use the 1st.

Same for the month
If the month IS known, it uses that month
If the month is UNknown, then it uses January.

 

[clickwhistle]

If I understand you correctly, in the event the year is known, date & month unknown will list as : 01-Jan-known year and so on.

My problem is this, I have actual dates that are 01-Jan-yyyy...I just wanted some way to differentiate between the dates that are estimates vs those that are known.

 

[Jonmo1]

So you also have some dates with unkown YEAR?
Will that unkonwn year be represented with 01-Jan-????

 

[clickwhistle]

No no, sorry for the confusion. With the substitution you've provided my unknown dates will be listed as "01-Jan-2010"

BUT, in some cases I have known events occurring on 01-Jan-2010. With the substitution I'd have to find some way to differentiate between which dates actually occured on 01-Jan-2010, vs those that were substitutions. Which is what i was trying to avoid.

 

[Jonmo1]

I would suggest an additional formula to test...

Say your original data is in A1
In B1 put the formula I suggested
=SUBSTITUTE(SUBSTITUTE(A1,"???","Jan"),"??",1)+0

In C1, put
=IF(B1=A1,"Real Date","Estimated Date")