Inputting date always in the past

[jeff88]

Wondering if there is a formula available that will always give a historical date.

I have a worksheet that I manually input the date. The date will always be in the past as it's a report for completed work. What I am trying to do is input the month/date and return the historical year (not necessarily this year). For example, if I input 1/12 Excel will automatically display 1/12/24 because of my formatting. What I want though is if I input something like 5/2 or 11/14 to display 5/2/23 or 11/14/23. Currently it will display with a year 24.

Bonus points if I manually enter a really old year like 2022 or 2021 the formula doesn't force it to 2023!

 

[Joe4]

You can use a Worksheet_Change event procedure code, which will automatically change the value as you manually enter values in some pre-determined range.
For example, if you wanted to apply it to all new entries in column M, the code might look like this:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to make sure only one cell updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   See if value entered in column M
    If (Target.Column = 13) And (IsDate(Target.Value)) And (Target.Value > 0) Then
'       Check to see if value is after today
        If Target.Value >= Date Then
'           Subtract one year from date
            Application.EnableEvents = False
            Target.Value = DateSerial(Year(Date) - 1, Month(Target), Day(Target))
            Application.EnableEvents = True
        End If
    End If

End Sub

Note that in order to fire automatically, this code needs to be placed in the proper sheet module. An easy way to ensure that is to go to the sheet you want to update, right-click on the sheet tab name at the bottom of the screen, select "View Code" and paste this code in the VB Editor window that pops up.

 

[jeff88]

You can use a Worksheet_Change event procedure code, which will automatically change the value as you manually enter values in some pre-determined range.
For example, if you wanted to apply it to all new entries in column M, the code might look like this:

Note that in order to fire automatically, this code needs to be placed in the proper sheet module. An easy way to ensure that is to go to the sheet you want to update, right-click on the sheet tab name at the bottom of the screen, select "View Code" and paste this code in the VB Editor window that pops up.

Hi Joe4, thanks for the reply. I forgot to mention that I am using the web app for this project. Any chance you have a script that I can use?

 

[felixstraube]

Why don't you just enter the complete date like "11/14/2023"?

 

[jeff88]

Why don't you just enter the complete date like "11/14/2023"?

I have to do a bunch at a time every single day so the one little automation would just be helpful.

 

[Joe4]

Hi Joe4, thanks for the reply. I forgot to mention that I am using the web app for this project. Any chance you have a script that I can use?

Pretty important detail!
No, I have never written any Office Scripts.
Sorry cannot help you there.