Filling in part of date on Excel automatically

Like dd/mm/2017

Didn't find your answer?

So say I am inputting 900 entries to Excel and they are all for June 2017.  I will absolutely not manage to type all 900 dates (and the average 8 key presses per date) on my first try, so I wondered if it was possible to tell Excel that my data would be in the format DD/06/17.

I tried to put that as a custom format but it says I can't...

I want to type 18 and it change to 18/06/17 basically.

Can I?

Replies (6)

Please login or register to join the discussion.

By Duggimon
07th Nov 2018 12:53

So you put your numbers in one column, auto formatting as dates, then in the column next to it have a formula that says =[thatothercell]+42886 and format that as a date as well, should give you what you're after.

nb: 42886 is the number of days between 1/1/1900 and 31/5/17, for a different month you'll need a different number, you can work it out by doing one date minus another in a cell formatted as a number format.

Thanks (0)
avatar
By Lisa R
07th Nov 2018 13:17

This also seems to work if you don't mind some extra columns:
Column A: 1 (or 2,3 etc)
Column B: Jun 17
Column C: =A1+B1-1

Thanks (0)
avatar
By daniel_
07th Nov 2018 14:49

Similar to others but I'd have column A with the day number only and column B as =DATE(2017,06,B2)

Thanks (0)
d
By puzzel
07th Nov 2018 16:10

Type your date 17/6 e.g
Once the batch has been input, highlight the date range.
Select find & select then replace 2018 with 2017, replace all

Thanks (0)
Replying to puzzel:
avatar
By Gone Sailing
07th Nov 2018 19:49

absolutely

Thanks (0)
avatar
By emanresu
09th Nov 2018 18:01

Then, after you've done any of the above, highlight all of the dates generated, type Ctrl-C then Alt-e-s-v then return.

That way the data you want wont vanish as soon as you delete your typed in column of temporary data.

Thanks (0)