PDA

View Full Version : Solved: Date Formatting



traveler7739
10-28-2010, 02:56 PM
Hello Everyone,

I need your help formatting a date. I would like a user of a spreadsheet to enter six digits in a cell and have the cell display the date (Ex: If the user entered 102810, the cell would display 10/28/10). This seems straight forward but I can't seem to get this to work. Can you help?

Thank you in advance for any assistance you can provide!!

frank_m
10-28-2010, 04:07 PM
Hi Traveler,

The distinguished Chip Pearson wrote a nice routine for that. - You put the code in your sheet change event.
I have used it and it works really well. For example if you have your regional settings set to mm/dd/yyyy and type 12810, or 012810, or 1282010, or 01282010, the result is 01/28/2010.
- Just change the cell range to suit your need:
http://www.cpearson.com/excel/DateTimeEntry.htm

macropod
10-28-2010, 04:08 PM
Hi traveler,

Please post your code. Simply telling us "I can't seem to get this to work" really doesn't give us anything to work with.

traveler7739
10-28-2010, 07:16 PM
Thank you and the distinguished Chip Pearson very much!! The code works perfectly!!

Thank you again!!!

frank_m
10-28-2010, 08:24 PM
Hi Traveler,

Glad to hear you are happy with "Chip's" code. He is quite clever.

One thing to mention though, you might be best off adding a replace or substitute function to check for and remove slashes or dashes before Chip's code runs, as I noticed that if the user types in slashes or dashes the results are wrong.