12

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?

Peter Mortensen
  • 12,190
ClaraA
  • 123
  • 1
    Welcome to Super User. Can you tell us what you have researched and already attempted? – CharlieRB Dec 14 '15 at 20:07
  • I've tried to use a non-date formular to Concatenate text: =CONCATENATE (##,"/",##."/",####). I don't think this is even close though. – ClaraA Dec 14 '15 at 20:10
  • 4
    there 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 Dec 14 '15 at 20:18
  • 3
    I'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 Dec 15 '15 at 05:47

3 Answers3

23

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.

enter image description here

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

CharlieRB
  • 22,754
  • Thank you. This worked, and I do understand that it is now not considered a date. – ClaraA Dec 14 '15 at 20:56
  • 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 Dec 15 '15 at 22:20
8

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
  • 27,892
  • Thank you. I got a circular error, so I am still trying to figure this out. – ClaraA Dec 14 '15 at 21:18
  • 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 Dec 23 '15 at 06:46
2

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
  • 592