Date Default to Last Year

Topic

New Reply

I’m making a list in Excel where 1 column contains dates. I want the format mm/dd/yy (no problem yet). However, I’d like to fill out the Date column by putting in just mo/day & have the year automatically fill in as last year (i.e. 2000). Currently, it completes the date as 2001. How can I change this just for selected cells in a column?

Thanks in advance.

Reply | Quote

Replies

Phil,

Excel defaults to the current year if you omit the year when entering a date. If that could be changed (I don’t think so) it would only be useful if you applied it to all dates.

If the date is purely cosmetic, and not used for date calculations you could try the a custom format, I think mm/dd”/2000″ would provide what you are looking for. However the underlying value of the date would still be the current year.

Another possibility would be to insert an additional column with a formula subtracting 365 from the column with the entered date. When all the data is entered you could convert the formulas to values and delete the original column.

These the only ideas I can come up with just now.

Andrew C.

Reply | Quote

Thanks, Andrew.

I only use Excel ocassionally (once a year to do helps with taxes). The Auto Complete (or whatever it’s called in Excel) wants to put 01 after I enter mm/dd & press Enter. I do sort by various columns, inlcuding date, so it’s not entirely cosmetic. I was just wishing there was a way to change the default for a single worksheet.

Reply | Quote

Dangerous – but you could just change your system clock back to 2000 while you work on that one sheet…

Reply | Quote

Edited by Phil Rabichow on 01/03/25 09:50.

Hi Lief:

Yea, I thought of that. But then other files will get the wrong access or creation date; probably wouldn’t hurt though. You’d think that Excel would allow you to create a default year for a worksheet; I’m sure there are accountants who use it. Thanks for the reply; I appreciate it.

Reply | Quote

If you don’t want to go as far as implimenting Hans’ solution, Find & Replace would probably to the job.

Reply | Quote

Now that’s a good idea–& simple too. I’d just have to pretend that all dates were current until the end. I wonder why I didn’t think of that before. Too emotionally involved, I suppose.

Reply | Quote

If you didn’t want to confuse yourself whilst entering the data, just format the cells for mm/dd until you’re finished.

I have found that Excel is relatively amenable to date-data entry. For example, entering 25-3 will give you 25-Mar-2001 – i.e. you dont need to enter 2 digits for the month. Likewise, entering 25-3-0 will give me 25-Mar-2000.
If your formatting uses ‘/’ dividers, I think you can still enter the date using a ‘-‘ and vice-versa.

BTW – I think subracting 365 from the date may only work back to March 1st last year – wasn’t 2000 a leap year? To make any of the solutions work, you may have to manually enter any entries for 29-Feb-2000 in full….

Reply | Quote

This is a little bit more complicated and I don’t know if it is worth the pain, but you can do it using the worksheet_change event of the worksheet where you want to enter these dates. The code below should be placed in the worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim d As Integer
Dim m As Integer
Dim ss As Date
Dim R As Range
Dim IntersectR As Range
Set R = Range(“A:A”)
Set IntersectR = Application.Intersect(R, Target)
If Not IntersectR Is Nothing Then
d = Day(IntersectR.Value)
m = Month(IntersectR.Value)
ss = d & “/” & m & “/” & “2000”
Application.EnableEvents = False
IntersectR.Value = ss
IntersectR.NumberFormat = “dd/mm/yyyy”
Application.EnableEvents = True
End If
End Sub

As an example, I assumed that your dates are entered in column A of the worksheet. If you enter something like 20/03 in a cell in column A, Excel takes 2001 as the default year. I am deriving the day and month from your input (which has 2001 as the year by default) and change the year to 2000 and display your date with 2000 as the year. Now, the full date has 2000 as the year and no longer 2001. Of course, if you want to enter a date with a year differently from 2001, the code will change that to 2000. With some if … then statements, you can filter out only those dates with 2001 as the year. Excuse me for the European date formats.

Reply | Quote

Hi Hans:

Thanks for the macro. I tried it, switching the mm & dd & switching 2000 to 00. It works most of the time. If I type, 12/22 & press enter, it works. If I press 12/22/ & press enter I get an error–& it then switches to 12/30/00. I can’t delete that value either; it keeps coming back (like the Jason tab in Word).

Reply | Quote

It is clear that my solution is far more complex than the one proposed by Leif. I agree that the best solution is always the most simple one. But, to answer your question, I added some code to check if your entry is a date. If not, the entry is undone and a messagebox comes up to warn you that you did not enter a date.
It gives you a very good idea of what you can do with this event if you are considering data validation rules which cannot be programmed via the Excel Data >> Validation menu.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim d As Integer
Dim m As Integer
Dim ss As Date
Dim R As Range
Dim IntersectR As Range
Set R = Range(“A:A”)
Set IntersectR = Application.Intersect(R, Target)
If Not IntersectR Is Nothing Then
If IsDate(IntersectR.Value) Then
d = Day(IntersectR.Value)
m = Month(IntersectR.Value)
ss = d & “/” & m & “/” & “2000”
Application.EnableEvents = False
IntersectR.Value = ss
IntersectR.NumberFormat = “dd/mm/yyyy”
Application.EnableEvents = True
Else
Application.EnableEvents = False
Application.Undo
MsgBox “This is not a date”
Application.EnableEvents = True
End If
End If
End Sub

Reply | Quote

Thank you so much, Hans. You’re solution, while more unforgiving than Lief’s (if I accidentally enter m/d/ as opposed to m/d, I get an error message), works. Now I’ve got at least 2 ways to do this.

Reply | Quote

Phil,

A slight modification to Hans’s code will auto correct your tendancy to add an extra “/”. I hope (a) Hans does not mind, and ( it works for you. Why should you have to change your habits to suit Excel. Amendments in red.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim d As Integer
Dim m As Integer
Dim ss As Date
Dim R As Range
Dim IntersectR As Range
Set R = Range("A:A")
Set IntersectR = Application.Intersect(R, Target)
If Not IntersectR Is Nothing Then
On Error Resume Next
If Right((IntersectR.Value), 1) = "/" Then
IntersectR.Value = Left(IntersectR.Value, (Len(IntersectR.Value)) - 1)
End If
On Error Goto 0
If IsDate(IntersectR.Value) Then
d = Day(IntersectR.Value)
m = Month(IntersectR.Value)
ss = d & "/" & m & "/" & "2000"
Application.EnableEvents = False
IntersectR.Value = ss
IntersectR.NumberFormat = "dd/mm/yyyy"
Application.EnableEvents = True
Else
Application.EnableEvents = False
Application.Undo
MsgBox "This is not a date"
Application.EnableEvents = True
End If
End If
End Sub

Andrew

Reply | Quote

Thank you so much, Andrew. I’d be in heaven if it wasn’t for the fact that I hate working on my taxes. (At least I don’t have to worry about dates though. I’m going to play with it & put in an error handler, so I can delete a date. But it works great.

Reply | Quote

Hi Phil,

Unless you want to DIY, you can just try the following amended version:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim d As Integer
Dim m As Integer
Dim ss As Date
Dim R As Range
Dim IntersectR As Range
Set R = Range("A:A")
Set IntersectR = Application.Intersect(R, Target)
If Not IntersectR Is Nothing Then
On Error Resume Next
If Right((IntersectR.Value), 1) = "/" Then
IntersectR.Value = Left(IntersectR.Value, (Len(IntersectR.Value)) - 1)
End If
On Error GoTo 0
If IsDate(IntersectR.Value) Then
d = Day(IntersectR.Value)
m = Month(IntersectR.Value)
ss = d & "/" & m & "/" & "2000"
IntersectR.Font.ColorIndex = xlAutomatic
Application.EnableEvents = False
IntersectR.Value = ss
IntersectR.NumberFormat = "dd/mm/yyyy"
Application.EnableEvents = True
Else
Application.EnableEvents = False
IntersectR.Font.ColorIndex = 3
MsgBox "This is not a date"
Application.EnableEvents = True
End If
End If
End Sub

This allows deletion of a cell, or range of cells, but does allow an incorrect value to remain in place. However such values will appear in red, so you may want to delete the “This is not a date” warning.

Andrew

Reply | Quote

Hi Andrew:

Thank you!!! This is really great! Now I don’t suppose you have a macro that will pay my taxes without the money coming from me

I had to look up DIY at http://www.acronymfinder.com/

Reply | Quote

No problem Andrew. Modify as you wish. My code only points in a direction of a possible solution. It is clear that modifications specific for your own particular needs are necessary.

Reply | Quote

If you use the Date() function in the cell you want to show your proper date you can put Date($X$1,y1,z1). You then insert the year you want in x1 which is the only cell you need for the year and create two extra columns in your sheet y and z, say, where you insert the month no and day no. Hey presto! the date you want appears in the appropriate cell.

Reply | Quote

Thanks, Herworth. Always nice to have alternatives. I’m set to go (of course, taxes are done this year, thank goodness).

Reply | Quote