Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
How do I convert dd/mm/yy date format to yyddd Julian date format | Excel Worksheet Functions | |||
Convert date + time text format to date format | Excel Worksheet Functions | |||
code to convert date from TEXT format (03-02) to DATE format (200203) | Excel Programming | |||
Change a date in text format xx.xx.20xx to a recognised date format | Excel Programming |