Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Alternate way to enter dates in Excel

Posted on 2015-02-02
7
Medium Priority
?
87 Views
Last Modified: 2016-02-11
I have someone who will be entering about a thousand dates in an Excel spreadsheet.  Is there a way to enter the date as:

1. mmddyy,
2. yymmdd
3. or in any other order that DOES NOT require entering a / or - or some other separator?
0
Comment
Question by:Bill Golden
7 Comments
 
LVL 16

Accepted Solution

by:
Carol Chisholm earned 1000 total points
ID: 40583792
You could have them entre them in any format and use a formula to convert to a date format.
However it is best to use the numeric keypad to enter them with a / or - so that they can see that date is valid before going onto the next one.
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40583796
Hi

These are the basic ones to start with;
date format
Without dashes
without dashes
0
 
LVL 16

Expert Comment

by:Carol Chisholm
ID: 40583806
If your date is in A1 and is in the format yyyymmdd then the formula to make a date is =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 1

Author Comment

by:Bill Golden
ID: 40583810
Sorry, she is working in Excel 2003.....

And formatting the cell *3/14/2001 does not work....
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40583811
did you try like this?
format
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40583818
For the 2003 version, you will go under Format / Cells.
Make sure to select the column you want to apply your new format before you change the format.

formats
0
 
LVL 37

Expert Comment

by:Kimputer
ID: 40584275
Use VBA, when clicked on the row or column that needs a date, open a userform with a MS Monthview control. NO typing needed on the user side, no FORMATTING errors by the user. Of course, that means you can't yell at the anymore like "OMG you switched the day with the month", since the user probably didn't make that mistake to begin with, or at least very not likely.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question