[Revenooer]

Hello all.

I am using Excel 2003 and often need to enter dates in the first column. The dates are rarely sequential, but often are in the same year. I really hate having to type in day, month, year in each row.

Typing just day/month will automatically default to current year. What I would really like is for a new entry to default to a specific year of my choosing (I am entering dates for years prior to current year) so that all I need to do is enter day and month.

Using a date format that only shows day and month is not a good solution because the printed worksheet requires dd/mm/yyyy format.

I know from playing with Access some 25 or so years ago that this would not be difficult; but finding how to do it in Excel has been a real pain.

The REAL killer is I could swear that I saw a tip book about 5 or 6 years ago where the author mentioned how to do this.

To recap:

I would like to enter day/month in column A (I typically utilize lists) and when I enter the next row of data, have the YEAR value in the cell default to either the year in the cell above (ideally) or from a value obtained from another cell. Heck. even default to some predeterimed year value as Excel does now with current year.

thanks for any assistance in advance

[Advertisements]

Hello Revenooer,

The only method that I can think of to do this is to use a Worksheet_Change method. The following method will allow you to just enter month and day (which defaults to the current year, as you have said) in column A, and then it will subtract one year from the resulting date. I added some crude (and hardly tested) handling for leap years as well, but you'll probably still need to add in the year for that date.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Do nothing if more than one cell is changed or content deleted
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    
    Dim strValue As String
    strValue = Target
    
    ' Stop any possible runtime errors and halting code
    On Error Resume Next

    ' Turn off ALL events so as not to put the code into a loop.
    Application.EnableEvents = False
    
    If Target.Column = 1 Then
       ' Ensure target is a date
        If IsDate(strValue) Then
            strValue = DateAdd("yyyy", -1, strValue)
        Else
            ' Check for leap year
            Dim strArray() As String
            strArray = Split(strValue, "/")
            If strArray(0) = "2" And strArray(1) = "29" And CInt(strArray(2)) Mod 4 = 1 Then
                ' Handle leap year
                strValue = strArray(0) & "/" & strArray(1) & "/" & CInt(strArray(2)) - 1
            End If
        End If
    End If
    
    Target = strValue
    
    ' Turn events back on
    Application.EnableEvents = True

    ' Allow run time errors again
    On Error GoTo 0
End Sub

You'll have to add this to the specific sheet in VBA (Alt-F11). I cannot guarantee that it will work as you expect, so you'll need to use it at your own risk.

Regards,

Ax

[Ax238]

Hello Revenooer,

The only method that I can think of to do this is to use a Worksheet_Change method. The following method will allow you to just enter month and day (which defaults to the current year, as you have said) in column A, and then it will subtract one year from the resulting date. I added some crude (and hardly tested) handling for leap years as well, but you'll probably still need to add in the year for that date.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Do nothing if more than one cell is changed or content deleted
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    
    Dim strValue As String
    strValue = Target
    
    ' Stop any possible runtime errors and halting code
    On Error Resume Next

    ' Turn off ALL events so as not to put the code into a loop.
    Application.EnableEvents = False
    
    If Target.Column = 1 Then
       ' Ensure target is a date
        If IsDate(strValue) Then
            strValue = DateAdd("yyyy", -1, strValue)
        Else
            ' Check for leap year
            Dim strArray() As String
            strArray = Split(strValue, "/")
            If strArray(0) = "2" And strArray(1) = "29" And CInt(strArray(2)) Mod 4 = 1 Then
                ' Handle leap year
                strValue = strArray(0) & "/" & strArray(1) & "/" & CInt(strArray(2)) - 1
            End If
        End If
    End If
    
    Target = strValue
    
    ' Turn events back on
    Application.EnableEvents = True

    ' Allow run time errors again
    On Error GoTo 0
End Sub

You'll have to add this to the specific sheet in VBA (Alt-F11). I cannot guarantee that it will work as you expect, so you'll need to use it at your own risk.

Regards,

Ax

[Revenooer]

Ax -


I woke at 2 am because my nose was so stopped up I could not breathe and had trouble getting back to sleep so I came online and got the email about your reply.

Thanks for the well commented code!

I will give it a test when I get in to work later and if I can get it to work as I want I will consider how to make it available to all worksheets and all new workbooks. I create new workbooks and worksheets several times a week that need this to make my data entry easier.

I am really surprised that Microsoft did not include a simple means of changing the default year from current to something else the user might want.

Thanks for the reply.

[Ax238]

Sounds good, hope it works our for you!

[Revenooer]

I found something that works.

The key line is thus:

target.value = dateserial(2009,month(target.value),day(target.value)

no worries about leap year.

I tried putting 2000 in a cell that I named intYear so I could use it as

target.value = dateserial(intYear,month(target.value),day(target.value)


but for some reason, this line results in the year 2000 being used. Event when I change the value of the cell to 2018

it has been too many years since I played with VBA, so I am drawing a blank on how to use a reference to a particular cell in the worksheet. Once I do, I will consider my quest complete.

Thanks for the assist!!

[Ax238]

Try

Range("intYear")

[Revenooer]

Thank you so much!

I will share what I cobbled together with your assistance and some other ideas I found:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next
If Target.Column <> 1 then Exit Sub
If Target.Cells.Count <> 1 then Exit Sub
If VarType(Target.Value)=vbDate Then
Target.Application.EnableEvents = False
Target.Value = DateSerial(Range("B3").Value, Month(Target.Value), Day(Target.Value)
Target.Application.EnableEvents = True
End If
On Errror Resume 0
End Sub

It works just the way I want. I can even change the year in Cell B3 half-way through to use a different year. Perfecto.

THanks for your help!

[Ax238]

Short and sweet, beautiful. Nice work! Glad I could assist.