Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #110441

Re: date format

Show all headers | View raw

> On Sunday, 7 January 2018 09:16:05 UTC+13, bill k  wrote:
>> Is it possible to use a macro to have excel complete a date dd/mm  with the 
>> year depending on the month entered. I.e. if I enter 27/01  it will return 
>> 27/01/2018   but if I enter 06/11 it will return 06/11/2017  or  If the 
>> month is greater than 6 the year will be yyyy -1. Thanks Bill K
>
> Garry,
> We want to be able to enter dates at random between September last year or 
> for early Jan this year. Bill K

Right-click the sheet tab and choose 'View Code', then paste this into the code 
window...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim vDate
  If IsDate(Target.Value) Then
    Application.EnableEvents = False
      vDate = Split(Target, "/")
      If vDate(0) > 6 Then vDate(2) = vDate(2) - 1
      Target.Value = Join(vDate, "/")
    Application.EnableEvents = True
  End If 'IsDate(Target.Value)
End Sub

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

date format bill k <bill.kuunders@xtra.co.nz> - 2018-01-06 12:15 -0800
  Re: date format GS <gs@v.invalid> - 2018-01-06 16:15 -0500
  Re: date format bill k <bill.kuunders@xtra.co.nz> - 2018-01-06 20:10 -0800
    Re: date format GS <gs@v.invalid> - 2018-01-07 04:24 -0500
      Re: date format GS <gs@v.invalid> - 2018-01-07 04:30 -0500
    Re: date format "Peter T" <askformy@gmail.com> - 2018-01-09 21:52 +0000
  Re: date format Patrick <patrick@nowhere.invalid> - 2018-01-07 10:19 +0100

csiph-web