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

 

[jasonb75]

Has anyone a solution for this

The solution is to carry on doing it the way that you have been already,

i have to enter the format dd/mm/2022

Trying to find a workaround to reduce the number of keystrokes required will probably waste more time than it saves.

 

[Rick Rothstein]

You cannot do what you want with cell formatting as you are needing to physically change an entered value. You can do what you want using event code though. A couple of caveats first... one, I am not using a Mac and, two, the natural order of my dates is the reverse of yours... that means I cannot test this for you (it works on my system though and I have reason to believe it should work for you too). Also, you did not tell us the column where you are entering your dates so I assumed Column C (which is why my If..Then test the column against the number 3)... change as necessary. Here is the event code...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 Then
    If Target.Column = 3 Then
      If 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

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[jasonb75]

the natural order of my dates is the reverse of yours... that means I cannot test this for you (it works on my system though and I have reason to believe it should work for you too).

Tested with UK format dates, Rick. Your code works fine.

 

[Rick Rothstein]

Thanks for confirming my code works with UK formatted dates... now the next hurdle is will it work on a Mac (I know nothing about Macs nor its implementation of VBA)?

 

[russelldt]

You cannot do what you want with cell formatting as you are needing to physically change an entered value. You can do what you want using event code though. A couple of caveats first... one, I am not using a Mac and, two, the natural order of my dates is the reverse of yours... that means I cannot test this for you (it works on my system though and I have reason to believe it should work for you too). Also, you did not tell us the column where you are entering your dates so I assumed Column C (which is why my If..Then test the column against the number 3)... change as necessary. Here is the event code...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 Then
    If Target.Column = 3 Then
      If 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

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Yep, thanks - works perfectly with Mac. I have column k, so will change the target.column to 11.

many thanks for your help

 

[russelldt]

Yep, thanks - works perfectly with Mac. I have column k, so will change the target.column to 11.

many thanks for your help

 

[russelldt]

Occasionally i need to delete a date that i have previously entered, and leave the cell blank. Testing this out now, when i delete a date there is a date 30 December 1900 in the cell, and i can't delete the date. Would appreciate if you can suggest how to modify the VBA to solve this.

Thanks

 

[Rick Rothstein]

Try using this variation of my code...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 Then
    If Target.Column = 3 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]

Try using this variation of my code...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 Then
    If Target.Column = 3 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

A perfect solution, thanks again for your support