Written by Allen Wyatt (last updated February 7, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
Brian would like to enter a date by typing in just the month and day. Excel assumes the year to be the current year, but he would like to have the year to be the one for the next time that date occurs. For instance, if today is 6/1/18 and Brian types 7/1, Excel automatically assumes a year of 2018. If Brian types 5/1 on the same day, then Excel should assume a year of 2019, as 5/1 is already in the past. Brian knows he can use a formula to have the date display in a different cell but wonders how he can get Excel to default to this approach as he is typing dates into cells.
Short of playing around with your system date or doing some editing to dates after entry, the solution requires the use of a macro. The following event handler will do the trick:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Target = "" Then Exit Sub If IsDate(Target) Then If Target < Date Then If Year(Target) = Year(Date) Then Application.EnableEvents = False Target.Value = DateAdd("yyyy", 1, Target) Application.EnableEvents = True End If End If End If End Sub
In order to add this macro to your workbook, simply right-click on the tab for the worksheet where you want it operative and then choose View Code. In the resulting code window, add the above macro.
Now, anytime you make a change in the worksheet, it is checked to see if it is a date. If it is, and the date is before today's date, then a year is added to the date. Note that the code checks to ensure that the year of the date being entered is the same as the current year. Without this check, any date in the past—even dates many years in the past—would be incremented. This is probably not intended, so the check is included.
The macro will not affect dates entered into multiple cells at once, nor will it affect dates entered as text values.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13601) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
If you need to know the years in which a particular date occurred on a specific day of the week, there are a number of ...
Discover MoreGiven a particular week number for a year, you may want to figure out the date of the last day in that week. There is no ...
Discover MoreBecause Excel stores dates internally as serial numbers, it makes doing math with those dates rather easy. Even so, it ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-02-08 19:51:54
@nickie Sr.:
To expand on my previous comment from today:
If the fraction you enter can be interpreted as a date, Excel will usually convert it to date, otherwise it will treat it as text, but not as number.
You could, however, format the cells as fractions with as many digits in the denominator as you wish, and then fractions you enter in them will not be treated as dates but as numbers. Your post is quite old, so I am not sure that you will read my comment, but if you do, please let me know.
2024-02-08 19:43:28
@Rene:
True, usually, 9/12 is treated as date, and if you enter =9/12 - as a formula. The exception is if the cell is formatted as a fraction, by using one of the predefined fraction formats or a custom format e.g.,
# ??/??
In such case 9/12 will be treated as a fraction, and displayed as such. With the custom format as above, it would be displayed as 3/4 instead of 9/12 though. You can force the denominator to be whatever you wish, so in this case you could use format # ??/12 and get 9/12 displayed. Beware though, that the fraction will be rounded to the nearest one fitting the format, so 7/13 with format # ??/12 will be displayed as 6/12. With the format allowing one digit denominator (# ?/?) it would be rounded to 1/2.
2024-02-07 04:32:33
Rene
@ Willy Vanhaelen
9/12 is only a fraction if '=' is added first.
2019-07-15 06:17:06
Willy Vanhaelen
@nickie Sr
9/12 is eiter a fraction or a date, both numeric. If you want your entry to be displad as 9/12 you must enter it as text by first formatting the cell as text or you can enter 9/12 preceded by an apostrphe in any cell: '9/12. The apostrophe will not be displayd but it tells Excel you want your entry to be text.
2019-07-13 11:53:42
nickie Sr.
I have yet to hear how I can stop getting a unwanted date in a cell. All I want to do is add a number such as 9/12. This not meant to be a date or formula.
2018-12-22 14:47:49
J. Woolley
@Jim: Read the first paragraph of the tip. Of course, you're right; it is a silly problem, but none the less interesting.
2018-12-22 14:02:21
Jim
I have a collection of cells (actually, a column) formatted as 'mm/dd/yyyy'; if I type 5/25 in a cell, the cell displays 05/25/2018 if the current year is 2018, as I would expect. However, if I type 5/25/19, the cell displays 05/25/2019. I don't see the problem.
2018-12-22 12:52:49
J. Woolley
By the way, my solution (posted in the previous comment) uses:
xTarget.Value = DateSerial((y + 1), Month(v), Day(v))
When the current year is 2020 (leap year) and the current date is 6/1/20, entering 2/29 results in 3/1/21 and Undo yields 3/1/20, so there is no way to enter 2/29/20. This could be fixed, but I will leave this as an exercise for the reader.
The tip uses:
Target.Value = DateAdd("yyyy", 1, Target)
When the current year is 2020 (leap year) and the current date is 6/1/20, entering 2/29 results in 2/28/21 and there is no Undo; however, DateAdd("yyyy", -1, "2/28/21") yields 2/28/20.
2018-12-22 11:58:10
J. Woolley
There are three problems with the event handler described in this tip:
1. It becomes impossible to enter any date for the current year that is earlier than the current date.
2. Any cell with a formula that results in a date for the current year that is earlier than the current date will be changed to a constant date value advanced by one year. For example, if today is 6/1/18, then the formula =TODAY()-1 becomes the constant value equivalent to 5/31/19.
3. It only applies to a single worksheet. (Maybe this is a benefit, not a problem.)
Here is my solution, which addresses all three problems. (Apologies for poor VBA code format when posting comments here.)
1. Press Alt+F11 to open the Visual Basic Editor (VBE).
2. Under VBAProject > Microsoft Excel Objects, click ThisWorkbook and press F7 to open the Code pane.
3. Copy the following VBA code for ThisWorkbook (which applies to any worksheet in ThisWorkbook and ignores cells having a formula):
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count = 1 And Not Target.HasFormula And IsDate(Target.Value) _
Then YearCheck_Do Sh, Target, 0
End Sub
4. From the VBE menu, pick Insert > Module.
5. Copy the following VBA code for this new Module (which must be in the same VBAProject):
Public Sub YearCheck_Do(ByVal Sh As Object, ByVal Target As Range, ByVal UndoRedo As Integer)
Static xSh As Object, xTarget As Range
If UndoRedo = 0 Then
v = Target.Value
y = Year(v)
If y = Year(Date) And v < Date Then
Beep
Set xSh = Sh
Set xTarget = Target
Application.EnableEvents = False
xTarget.Value = DateSerial((y + 1), Month(v), Day(v))
Application.EnableEvents = True
s = "Undo '" + CStr(y + 1) + "' in " + Replace(xTarget.Address, "$", "")
Application.OnUndo s, (ThisWorkbook.Name + "!YearCheck_Undo")
End If
Application.OnRepeat "", ""
Else
ThisWorkbook.Activate
xSh.Activate
xTarget.Select
v = xTarget.Value
y = Year(v)
Application.EnableEvents = False
xTarget.Value = DateSerial((y + UndoRedo), Month(v), Day(v))
Application.EnableEvents = True
s = "' in " + Replace(xTarget.Address, "$", "")
If UndoRedo < 0 Then
s = "Redo '" + CStr(y) + s
Application.OnRepeat s, (ThisWorkbook.Name + "!YearCheck_Redo")
Else
s = "Undo '" + CStr(y + 1) + s
Application.OnUndo s, (ThisWorkbook.Name + "!YearCheck_Undo")
Application.OnRepeat "", ""
End If
End If
End Sub
Private Sub YearCheck_Undo()
YearCheck_Do Nothing, Nothing, -1
End Sub
Private Sub YearCheck_Redo()
YearCheck_Do Nothing, Nothing, 1
End Sub
Now if today is 6/1/18 and you type 7/1, Excel will assume the year 2018. But if today is 6/1/18 and you type 5/1 or 5/1/18 or 5/1/2018, the year will be changed from 2018 to 2019. If you wanted the year to remain 2018 instead of changing to 2019, then click Undo or press Ctrl+Z. The only way to enter a date for the current year that is earlier than the current date becomes a two-step process: First enter the desired date, then click Undo or press Ctrl+Z. After using Undo, it is possible to click Repeat of press Ctrl+Y (or F4) to Redo the change to 2019. This Undo/Redo only applies to the last date entered for the current year that was earlier than the current date. (Note: Excel does not permit VBA control of OnRedo, but OnRepeat has the same keyboard shortcuts.)
2018-12-22 05:15:33
Michael Avidan
Sorry... Waht I meant was:
Shouldn't it read: If Target > Date Then...
2018-12-22 05:14:26
Michael Avidan
I wonder....
Shouldn't it read: If Target < Date Then...
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments