Entering lots of dates without having to physically key the / (forward slash)

I enter a lot of columns of dates, for instance 05/12/1943. I would like it to appear as such, but I would only like to type 5121943. I am trying to conserve on my key strokes. I have been able to use the custom formula mm/dd/yyyy, and I only need to type 5/12/1943. I am repeating: I would like to not have to type the /.

I've tried to use a non-date formular to Concatenate text: =CONCATENATE (##,"/",##."/",####).

How can I achieve this?

microsoft-excel-2010

ClaraA

Posted 2015-12-14T19:40:10.850

Reputation: 133

1Welcome to Super User. Can you tell us what you have researched and already attempted? – CharlieRB – 2015-12-14T20:07:52.733

I've tried to use a non-date formular to Concatenate text: =CONCATENATE (##,"/",##."/",####). I don't think this is even close though. – ClaraA – 2015-12-14T20:10:30.163

4there are dates that are undefined after this - for example 1/23/xxxx and 12/3/xxxx are the same if you skip the /. You will need to find a way for those to be handled – Aganju – 2015-12-14T20:18:42.713

3I'd think using the numeric pad and actually typing the slashes is the fastest option. You could omit the slashes, but in order to be safe you'd have to disallow any ambiguous input, which would cost more time by breaking the flow than the omission of the slashes saved. – Simon Richter – 2015-12-15T05:47:29.267

Answers

Format your cells with the following custom format and you can then type in the consecutive numbers without typing the /.

00\/00\/000

For instance, if you type 12152015, when you leave the cell it will appear as 12/15/2015.

The caveat is this will be seen as a number, not a date.

CharlieRB

Posted 2015-12-14T19:40:10.850

Reputation: 21 303

Thank you. This worked, and I do understand that it is now not considered a date. – ClaraA – 2015-12-14T20:56:34.467

If you can live with writing your dates in this format 2015/12/15 (Year/Month/Day) then later on you can sort those numbers and they will be correctly sorted as if they are a date. – Mario Awad – 2015-12-15T22:20:27.783

Excel (unlike Access) does not have input masks.

You could use the formula below to convert the number 5121943 to the date 5/12/1943. You may need to specifically format the formula result as a date.

=DATE(MOD(A1, 10000),TRUNC(A1/1000000), MOD(TRUNC(A1/10000), 100))

Note: Rather than being flagged with an error, an invalid input date (not in mmddyyyy) will not display as intended:

13131948 ... 1/13/1949
  612015 ... 1/30/2015

Steven

Posted 2015-12-14T19:40:10.850

Reputation: 24 804

Thank you. I got a circular error, so I am still trying to figure this out. – ClaraA – 2015-12-14T21:18:44.010

1@ClaraA put the formula in B1, and input the value in A1. you can then copy B1, and paste special (value) to make the date permenant – wilson – 2015-12-23T06:46:26.807

You can get down to 3-8 keystrokes per date if you type them in the format dd-mm, or mm-yy for unambiguous 1st-of-the-month dates (i.e. where yy > 12), or dd-mm-yy for dates not within the current year and not on the 1st of the month. Numbers less than 10 can be entered as single digits. This will not apply the format you want, so you need to do that in advance (preferably to the whole column in one go) or after you've finished.

For (uniformly distributed) dates within the current year, this approach only requires on average 3.96 keystrokes per date, including the - characters - twice as fast as the methods in the other answers. The other special cases are also slightly faster.

user3490

Posted 2015-12-14T19:40:10.850

Reputation: 484