Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed with altering dates and date formats in VBA
Hi there I have a slight problem and I don't know how to do it.
I have these 3 columns in: Column A (January to December in a sequence) around 170 cells Column G has a calendar that pops up and displays dates (format dd/mm/ yyyy) (this is only filled in if actions are required) Column H that has dates in it in same dd/mm/yy format (only filled in automatically if they have filled in information in another cell in the same row) What I need it to do is if cells in column G & H are blank then it keeps the cells going in the same sequence down as it is already (but as Jan-07 format for example) If column G has input e.g. 31/04/07 then it changes column A to the following month e.g. May-07. And if column H has been filled in then it will change the date in A to a year after the date it originally had e.g. May-07 will become May-08. G will always take precedence over H if both are filled in. Any help with this would be greatly appreciated as I am a complete novice at the moment. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help needed with altering dates and date formats in VBA
Put the following in the code module for the worksheet. You said Col H
is filled in as a result of entry in another column. If so, you will need to change the 2nd half to use the appropriate column/offsets. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 7 Then 'col G Target.Offset(0, -6) = DateAdd("m", 1, Target) End If If Target.Column = 8 Then 'col H If IsEmpty(Target.Offset(0, -1)) Then Target.Offset(0, -7) = DateAdd("yyyy", 1, Target) Else Target.Offset(0, -7) = DateAdd("m", 1, Target.Offset(0, -1)) End If End If End Sub Hth, Merjet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates in different formats | Excel Discussion (Misc queries) | |||
Dates in different formats | Excel Discussion (Misc queries) | |||
dates and formats | New Users to Excel | |||
Macro behaves differently then when recorded and stuffs dates (or date formats) | Excel Programming | |||
Excel Addin altering date format on text file import | Excel Programming |