how to change th autocomplete for dates excel mac

I want Excel to automatically populate it as it does with 2012, and as it did on December 31st.

I feel I did do my due diligence before asking, otherwise Id have expected a lmgtfy answer.

Then when you are all done entering your dates, in a new column, enter the formula A1-365, and just copy the formula all the way down, assuming column A contains the dates that you entered.

It doesnt have to be 365.25, just something larger than 365 and smaller than 366.

That extra 18 hours could cause all sorts of issues if the value is used in any other formulas.

Apart from changing the system date, you can use a worksheet event formula.

In the column of options it offers for date format, select the date format that shows month spelled out, day number, comma, space, 4-digit year (i.e.: January 31, 2011).

Be sure there is a space before the 4-digit year.

In the other field for the data you wish to replace the old data with, enter the correct year.

Like the OP I needed to enter data from last year without having to type 11 each time, but not all of the dates I was entering were in 2011 (I was also entering dates in 2009 and 2010, for which I would have to manually enter the year anyway even if I hadnt procrastinated in 2011).

I would not want dates in 09-10 to be set back by a year along with the 2011 dates.

My solution was to set up a second adjacent column with the following formula.

Lets say Im typing dates into column A and made column B my corrected column; the formula in column B is.

This way, dates before 2011 will not be corrected, and we also account for the fact that 2012 is a leap year so any dates after 228 will be set back by 366 days instead of 365.

This is nice if you cant change the datetime in Windows because your institution doesnt give you administrative privileges on your own workstation.

It may be a few days after the original post but I hope this helps.

Ths version works on all sheets in a workbook (and thus must be placed into the workbooks script, not a worksheets) and also only changes entries which contain the current year.

However, you can EASILY highlight ALL the dates, even if the year is NOT SHOWING, and then change them all.

In my case, I needed the dates to be in 2018 but Im entering data in 2019.

Provide details and share your research But avoid Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

Not the answer youre looking for Browse other questions tagged excel date or ask your own question.

How To Change Th Autocomplete For Dates Excel Mac