Formatting for date input

[Craig92604]

I have users who enter date like this : 051205 this should represent 05/12/05 but in excel I get 03/10/40.

I have tried every possible avenue to change this but can't figure it out? Any help would be appreciatted

 

[Oaktree]

You could either:

1) Format A1 as text and use a different cell as =DATEVALUE(LEFT(A1,2)&"-"&MID(A1,3,2)&"-"&RIGHT(A1,2)) to get your date

or 2) Use VBA and a worksheet change macro to perform similar manipulations

Excel's date associated with the number 51205 is March 10, 2040, hence why formatting that number as a date gives you the "odd" result.

 

[Craig92604]

That is what I thought I came up with =(LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2))

 

[Oaktree]

...just make sure you use the datevalue command as well. Otherwise you will be storing the date as text, not an Excel date. As text, functions where you sum based on < a certain date or try to group in a pivot table might give you problems.

 

[Craig92604]

Thanks!