date format
 

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

date format
 

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

Excel will complete dd/mm entered dates with the current year. If the date
entered belongs to another year then the year must also be entered in the
system default date format.

--
Garry

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

date format
 

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

date format
 

On 2018-01-06 20:15:55 +0000, bill k said:

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

If you enter a date like 27/01, Excel will automatically complete the
date with the current year so the date will become 27/01/2018. Here's a
function that you can use :

Function NewDate(aDate As Date)
Dim theMonth As Integer
Dim theYear As Integer

theMonth = Month(aDate)
theYear = Year(aDate)

If theMonth 6 Then theYear = theYear - 1

NewDate = DateSerial(theYear, theMonth, Day(aDate))
End Function

You can easily turn this function in macro :

Sub NewDate(theDate as Range)
aDate = theDate.Value
[...]
theDate.Value = NewDate
End Sub

date format
 

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

date format
 

This assumes your cells are formatted as 'Date'!

--
Garry

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

date format
 

"bill k" wrote in message
...
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

Not clear if 1-July or 1-Sept is the key date, greater than 6 per your OP
but now September - confusing! Adapt the following, also the range if any to
limit looking at

if I enter 27/01 it will return 27/01/2018
normally if you enter 27/1 it will assume you're entering a date and also
assume you want it this year, and applied as a numeric date-value

' in the worksheet module
Private Const mDate As Date = #9/1/2018# ' adapt to suit but use US date
format

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dt As Date
If Not Intersect(Target(1), Range("A:A")) Is Nothing Then
' only look at changes in col-A, adapt to suit
If IsDate(Target(1)) Then
dt = Target(1)
If Target(1) = mDate And Year(dt) = Year(Date) Then
Target(1) = DateSerial(Year(mDate) - 1, Month(dt), Day(dt))
End If
End If
End If
End Sub

If you enter 1/9 (or 9/1 if US) the date should change from 2018 to 2017,
but 31/8 will remain 2018. As written 1/1/2019 will not change.

Peter T