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.
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.
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
Similar to others but I'd have column A with the day number only and column B as =DATE(2017,06,B2)
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
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.