Date format

[russelldt]

I have a cashflow sheet, and i need to enter dates that payments are due. If the date is in the following year, i have to enter the format dd/mm/2022, if the due date is after 31/12/2021.

How can i format the date to only look at dates that occur after todays date, so if i enter 12 Jan, the cashflow date is 12 jan 2022, and not 2021. This would apply to a large range of cells as well as some specific cells, not the entire sheet.

Has anyone a solution for this,

Many thanks

 

[russelldt]

A perfect solution, thanks again for your support

Hello Rick,

I have picked up an issue with my spreadsheet, and I am wondering if you can provide a solution for me.

I only need this formatting to start after Row 8, in Column K. I have links in cells above this (Row 8) that are linked to text fields in another sheet, and this formatting is automatically changing these to Dates.


Many thanks

 

[jasonb75]

Just need to add a second criteria to this line so that ignores the top rows.

If Target.Column = 3 And Target.Row >=8 Then

I wasn't sure from your post if row 8 should be the first one to format with the code, or if it should be the last one to exclude. If set it to start from row 8 but you can easily change that to 9 if needed.

 

[Rick Rothstein]

Hello Rick,

I only need this formatting to start after Row 8, in Column K.

Does this modification of my code do what you want (I highlighted the line of code I changed)...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 Then
    If Target.Column = 11 And Target.Row > 8 Then
      If Target.Value = "" Then
        Target.NumberFormat = "General"
      ElseIf Target.Value <= Date Then
        Application.EnableEvents = False
        Target.Value = DateAdd("yyyy", 1, Target.Value)
        Application.EnableEvents = True
      End If
    End If
  End If
End Sub

 

[russelldt]

Yes, Thanks Rick. And if I want to end at Row 64, do I modify the row as follows:

If Target.Column = 11 And Target.Row > 8<64 Then


Thanks

 

[Rick Rothstein]

No, that is not correct syntax. Try it this way...

If Target.Column = 11 And Target.Row > 8 And Target.Row < 64 Then

 

[russelldt]

Thanks Rick

 

[russelldt]

Thanks Rick

Hello Rick, it's me again, requesting a minor modification to this.

We sometimes need to revise dates to reflect actual dates that an event occurred. I am unable to do this for dates that have passed, so - for example today is 22 March 2022, I cant enter a date earlier than this, so if i enter 21 march 2022, it forces the date 21 march 2023. In effect, i want to be able to enter dates before today.

Hope this s possible,

Thanks