Macro: Add one month to all the cells with date data in workbook
Closed
fireburn
Posts
28
Registration date
Monday February 3, 2014
Status
Member
Last seen
June 23, 2014
-
Apr 27, 2014 at 12:56 AM
fireburn Posts 28 Registration date Monday February 3, 2014 Status Member Last seen June 23, 2014 - May 12, 2014 at 11:55 AM
fireburn Posts 28 Registration date Monday February 3, 2014 Status Member Last seen June 23, 2014 - May 12, 2014 at 11:55 AM
Related:
- Macro: Add one month to all the cells with date data in workbook
- Spell number in excel without macro - Guide
- Macro excel download - Download - Spreadsheets
- Automatically run a macro when opening a worksheet - Guide
- Excel macro to create new sheet based on value in cells - Guide
- Run macro when cell value changes - Guide
5 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Apr 28, 2014 at 12:44 AM
Apr 28, 2014 at 12:44 AM
try this
Sub test()
Dim c As Range
For Each c In Range("A1").CurrentRegion
If WorksheetFunction.IsNumber(c) Then c = Month(c) + 1 & "/" & Day(c) & "/" & Year(c)
Next c
End Sub
fireburn
Posts
28
Registration date
Monday February 3, 2014
Status
Member
Last seen
June 23, 2014
Apr 28, 2014 at 02:20 AM
Apr 28, 2014 at 02:20 AM
Thanks for replying, but it doesn't work with my worksheet.
The dates are on column A I formatted this to ddmmyyyy but when you click the cell it will show 3/1/2014 for 01032014.
The dates are on column A I formatted this to ddmmyyyy but when you click the cell it will show 3/1/2014 for 01032014.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Apr 28, 2014 at 06:57 AM
Apr 28, 2014 at 06:57 AM
It worked for me
it does not matter how you format it. how did you enter it-that is important
you must enter it according to regional configuration either m/d/yy or d/m/yy.
see control panel -region and language. in formula bar it will show as you have indicated. is that march 1st or 3rd january.
it does not matter how you format it. how did you enter it-that is important
you must enter it according to regional configuration either m/d/yy or d/m/yy.
see control panel -region and language. in formula bar it will show as you have indicated. is that march 1st or 3rd january.
fireburn
Posts
28
Registration date
Monday February 3, 2014
Status
Member
Last seen
June 23, 2014
May 2, 2014 at 01:07 PM
May 2, 2014 at 01:07 PM
Hi,
Its working, but the thing is, it will add numbers in all the cell that has numbers even it is not formatted as date.
Can you please help me to add one month on those in column A only.
I've tried
Sub test()
Dim c As Range
For Each c In Range("A:A").CurrentRegion
If WorksheetFunction.IsNumber(c) Then c = Month(c) + 1 & "/" & Day(c) & "/" & Year(c)
Next c
End Sub
Even changed it to IsDate
But it doesnt work.
Thank you.
Its working, but the thing is, it will add numbers in all the cell that has numbers even it is not formatted as date.
Can you please help me to add one month on those in column A only.
I've tried
Sub test()
Dim c As Range
For Each c In Range("A:A").CurrentRegion
If WorksheetFunction.IsNumber(c) Then c = Month(c) + 1 & "/" & Day(c) & "/" & Year(c)
Next c
End Sub
Even changed it to IsDate
But it doesnt work.
Thank you.
fireburn
Posts
28
Registration date
Monday February 3, 2014
Status
Member
Last seen
June 23, 2014
May 4, 2014 at 10:08 PM
May 4, 2014 at 10:08 PM
Hi,
Your code works when only column A has date value, but when there is a number in column B and so on, it will add something also to it.
I only want to change the dates in column A .
Thank you.
Your code works when only column A has date value, but when there is a number in column B and so on, it will add something also to it.
I only want to change the dates in column A .
Thank you.
Didn't find the answer you are looking for?
Ask a question
Sub test()
Dim c As Range
For Each c In Range("A1:A20")
If IsDate(c) Then c = WorksheetFunction.EDate(c, 1)
Next c
End Sub
Try this.
This uses the built-in function EDATE to achieve what you want.
It more accurate as the other method does not deal with dates that roll past 12 (December) correctly.
Change the range used accordingly
fireburn
Posts
28
Registration date
Monday February 3, 2014
Status
Member
Last seen
June 23, 2014
May 12, 2014 at 11:55 AM
May 12, 2014 at 11:55 AM
Thank you so much. It works.
