I’ve got a daily task where I need to update a spreadsheet with dates. Normally, I just put in the month/date, and it puts in the year. So last year, if I typed 12/14, it recognizes it as 12/14/15. Now, if it type in 12/14, it recognizes it as 12/14/16. Is there anyway for it to recognize December dates as 2015 and January dates as 2016, without also having to manually type in the year?
If you have a bunch of December dates that need entering, you can change the system clock back to a date in 2015. When entering month/day, it will assume it is 2015. Change the clock back to today, then enter all of the 2016 dates.
If you have a mix of 2015 and 2016 dates, I think you have to add the year to the 2015 dates.
One way you can try is enter the date as you were and then go back and selectively changing the appropriate years. You can use * as a wildcard in the search field, i.e. searching for 12/*/2016 will find all fields starting with 12 and ending with 2016. You may have to fiddle with the syntax depending on how the date is actually stored in the excel fields.
Be careful doing this. For 99% of everybody out there, it will be fine. But for the one guy who has some software with a weird license (typically a 90-day license or one with an evaluation period), it will detect you monkeying with the system clock and think you are trying to defeat the licensing agreement, and lock the software.
For that reason, and many others, its best to enter just a numeric date,
and work off the “headline” cells for month and year.
To the excel spread sheet looks like this, with > to show where a new cell starts
YEAR> 2015 The first day of the year this year is > 2015/1/1
MONTH > DECEMBER
NUMERIC MONTH> 12
Day Date(Formula) hours kg speed cost
3 2015/12/3 7.5 664 42342 $953
So the formula I can’t type out straight away, but since a date is the number of days since the midnight starting 1970/1/1 (although its bad form to rely on the epoch, hence the need for the cell containing the date representing 1/1 that year. ), its just a few +'s and -'s. (you can
This makes entering the day of the month faster, and prevents the problem of excel showing you the date as 7/6 , and leaving you with two possible mistakes 1… what year is it ? Its hiding the year, and if you then go on to rely on its year value… bad data… 2. Is it July or June ? It could well be close enough in date to be “not obviously wrong”.
Another option is to enter the dates without a year (let Excel default 2016) and then use a formula to “correct” them:
=IF(MONTH(A1)=12,A1-366,A1)
Remember 2016 is a leap year, so you need to subtract 366.
Don’t be lazy; put in the year as well. Remember that computers are precise and stupid.
Seriously. I mean, what are you going to do in 2017? And when would your cutoff date to be 2015 be - what’s the first date you entered like that, 8/15 or something? So what happens come 8/15/2016?
Or can you confidently say this a one-off scratchpad type of spreadsheet you’re going to be completely done with in a few weeks?
Here’s what I would do to change all your MM/DD dates to proper dates.
Say your date column that you’ve been entering into as MM/DD is column “A”. You apparently started doing this some time in 2015, so all your dates except the ones from January so far this year are really froom 2015, but anything with an “01” month value is 2016. And you want to change your dates to be correct, and you will henceforth enter dates properly with the year included, like a human being.
First, highlight the column and format all your dates to be the year-inclusive format of your choice, so you can see what the year is.
Next, insert a new (blank) column B to the right, and in the first date row (assuming it’s B1 next to A1), enter a formula in B1 as:
=IF(TEXT(A1,“MM”)=“01”, DATE(“2016”, TEXT(A1, “MM”), TEXT(A1, “DD”), DATE(“2015”, TEXT(A1, “MM”), TEXT(A1, “DD”))
Now all your January dates are known to be 2016, and anything else is “yeared” as 2015. (Column B may be just a date-as-number like “42339”, but if you highlight the column and format it, you’ll see whatever date format you want to see it as.)
Now highlight all the dates in Column B, and paste it over in Column A - but “Paste Special - Values” (i.e., don’t copy over the formula, but the formula results), and delete Column B.
nm
keep it simple. highlight the ones you want to change and format them as mm/dd/yyyy. Then do a bulk “replace as” and replace 2016 with 2015. Reformat it as needed for presentation.