Excel want recognize dates

Posted by Bill on November 01, 2001 5:15 AM

I have some data that has dates entered as Sep 21, Oct 01, Excel does not think these are dates, is there a way to add a year "2001" to them so Excel will recognize them as dates?, except retyping them by hand. Thanks

Posted by Todd on November 01, 2001 5:40 AM

This should work.. if date is in a1 type this formula:
= a1 & ", 2001"

Posted by Bill on November 01, 2001 6:21 AM

This "looks" right but they are not dates, if you try to sort them it sorts by A-Z, Apr to Oct instead of Jan to Dec

formula:

Posted by Todd on November 01, 2001 7:01 AM

Use the Datevalue funtion. If you use it on "Sep 21" then it should assume 2001. If you want to assume a different year, do my & technique, then use the datevalue function on it.

Posted by Todd on November 01, 2001 7:02 AM

one more thing

I meant to add that the trick is getting excel to recognize the text as a date and convert the date into its date coding. e.g. Sep 21 is 37155. From then, you can format the cell to make it look however you like, and it sorts based on consecutive dates, not April-Oct.

Posted by Bill on November 01, 2001 7:41 AM

Thanks Todd I got it