Formatting for date input

Craig92604

Well-known Member
Joined
Nov 7, 2002
Messages
505
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
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

Well-known Member
Joined
Nov 7, 2002
Messages
505
That is what I thought I came up with =(LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2))
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
...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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top