Increment Month/Year in "mmm-yy" format
I've written a sub to increment the month to the next one, so that Jan-61 -
Feb-61... Dec-61 - Jan-62, etc, whenever a date is in the cell (value in the formula bar shows UK-style date format dd/mm/yyyy, but formatted as "mmm-yy" in the cells). The code is copied below. What's driving me mad is that it works for years up to 1999, it then changes Dec-99 to Jan-00 Ok, but then it changes Dec-00 to Jan-04 and thereafter the year "sticks" at 2004, although the months cycle round OK. I've used F8 to watch each step, but I can't fathom what's going wrong. I think it may have something to do with the day, because for dates up to 2000, the formula bar shows 01/mm/yyyy, but after that shows 04 or 05. Is this some weird date format thing - or a UK/US date incompatibility? Very grateful if somebody could tell me where my code is going wrong. Sub NextMonth() Dim UsedArea As Range Dim c As Range Dim ThisMonth As Integer Dim ThisYear As Integer Dim NextMonth As Integer Dim NextYear As Integer Set UsedArea = ThisWorkbook.Worksheets("Test Sheet").UsedRange For Each c In UsedArea.Cells If IsDate(c.Value) Then ThisMonth = Mid(c.Value, 4, 2) ThisYear = Right(c.Value, 4) End If If ThisMonth = 12 Then NextMonth = 1 NextYear = ThisYear + 1 Else NextMonth = ThisMonth + 1 NextYear = ThisYear End If c.Value = Format(NextMonth & "-" & NextYear, "mmm-yy") Next c End Sub |
Increment Month/Year in "mmm-yy" format
First, your using Format() returns a string. XL's parser interprets
Jan-05 as 5 January of the current year. Using Format() has no effect on what is displayed in the cell - that's determined completely by the number format and how the parser interprets the input. Here's one alternative: Public Sub NextMonth() Dim c As Range For Each c In Sheets("Test Sheet").UsedRange If IsDate(c.Value) Then With c .Value = DateSerial(Year(.Value), Month(.Value) + 1, 1) End With End If Next c End Sub In article , "Ian Ripsher" wrote: I've written a sub to increment the month to the next one, so that Jan-61 - Feb-61... Dec-61 - Jan-62, etc, whenever a date is in the cell (value in the formula bar shows UK-style date format dd/mm/yyyy, but formatted as "mmm-yy" in the cells). The code is copied below. What's driving me mad is that it works for years up to 1999, it then changes Dec-99 to Jan-00 Ok, but then it changes Dec-00 to Jan-04 and thereafter the year "sticks" at 2004, although the months cycle round OK. I've used F8 to watch each step, but I can't fathom what's going wrong. I think it may have something to do with the day, because for dates up to 2000, the formula bar shows 01/mm/yyyy, but after that shows 04 or 05. Is this some weird date format thing - or a UK/US date incompatibility? Very grateful if somebody could tell me where my code is going wrong. Sub NextMonth() Dim UsedArea As Range Dim c As Range Dim ThisMonth As Integer Dim ThisYear As Integer Dim NextMonth As Integer Dim NextYear As Integer Set UsedArea = ThisWorkbook.Worksheets("Test Sheet").UsedRange For Each c In UsedArea.Cells If IsDate(c.Value) Then ThisMonth = Mid(c.Value, 4, 2) ThisYear = Right(c.Value, 4) End If If ThisMonth = 12 Then NextMonth = 1 NextYear = ThisYear + 1 Else NextMonth = ThisMonth + 1 NextYear = ThisYear End If c.Value = Format(NextMonth & "-" & NextYear, "mmm-yy") Next c End Sub |
Increment Month/Year in "mmm-yy" format
Many thanks. As usual, the solution is much easier than I was trying to do
it. I should have twigged the DateSerial function. "JE McGimpsey" wrote in message ... First, your using Format() returns a string. XL's parser interprets Jan-05 as 5 January of the current year. Using Format() has no effect on what is displayed in the cell - that's determined completely by the number format and how the parser interprets the input. Here's one alternative: Public Sub NextMonth() Dim c As Range For Each c In Sheets("Test Sheet").UsedRange If IsDate(c.Value) Then With c .Value = DateSerial(Year(.Value), Month(.Value) + 1, 1) End With End If Next c End Sub In article , "Ian Ripsher" wrote: I've written a sub to increment the month to the next one, so that Jan-61 - Feb-61... Dec-61 - Jan-62, etc, whenever a date is in the cell (value in the formula bar shows UK-style date format dd/mm/yyyy, but formatted as "mmm-yy" in the cells). The code is copied below. What's driving me mad is that it works for years up to 1999, it then changes Dec-99 to Jan-00 Ok, but then it changes Dec-00 to Jan-04 and thereafter the year "sticks" at 2004, although the months cycle round OK. I've used F8 to watch each step, but I can't fathom what's going wrong. I think it may have something to do with the day, because for dates up to 2000, the formula bar shows 01/mm/yyyy, but after that shows 04 or 05. Is this some weird date format thing - or a UK/US date incompatibility? Very grateful if somebody could tell me where my code is going wrong. Sub NextMonth() Dim UsedArea As Range Dim c As Range Dim ThisMonth As Integer Dim ThisYear As Integer Dim NextMonth As Integer Dim NextYear As Integer Set UsedArea = ThisWorkbook.Worksheets("Test Sheet").UsedRange For Each c In UsedArea.Cells If IsDate(c.Value) Then ThisMonth = Mid(c.Value, 4, 2) ThisYear = Right(c.Value, 4) End If If ThisMonth = 12 Then NextMonth = 1 NextYear = ThisYear + 1 Else NextMonth = ThisMonth + 1 NextYear = ThisYear End If c.Value = Format(NextMonth & "-" & NextYear, "mmm-yy") Next c End Sub |
All times are GMT +1. The time now is 10:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com