Increase integer by +1 based on date

I want to increase all values in a column by 1 on the 1st of each month (E.g. when it becomes the 1st May, the first value in my column will go from 10 to 11).
Louise HitchcockAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
HI,

you could use a formula
=IF(MONTH(A3)=MONTH(A2),B2,B2+1)

Open in new window

or try
Sub macro()
For Each c In Range(Range("A2"), Range("A" & Rows.Count))
    If Month(c.Offset(-1).Value) = Month(c.Value) Then
        c.Offset(, 1).Value = c.Offset(-1, 1).Value
    Else
        c.Offset(, 1).Value = c.Offset(-1, 1).Value + 1
    End If
    
Next
End Sub

Open in new window

Regards
0
Louise HitchcockAuthor Commented:
I'm afraid I couldn't get this to work. Essentially I have a column of numbers which need to automatically increase by 1 on the 1st of each month. The numbers refer to how long someone has been with an organisation. Is there a way to do this automatically using VBA or a formula? I don't have the months written down on the excel sheet at present, just the column with the numbers in.

Thank you in advance for any further guidance!
0
Rgonzo1971Commented:
Could you send a dummy?
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Louise HitchcockAuthor Commented:
I've uploaded a dummy file, however there's not much to go on as it's just a column of data at present!

Thanks!Tenure.xlsx
0
Rgonzo1971Commented:
Not enough information
0
Louise HitchcockAuthor Commented:
Unfortunately I cannot provide more information as that's the data I'm working with. Every time I open the file, I need the file itself to recognise when we've hit the first of the month so that it can increase the integer by 1. Not sure what else I can provide to help with this?

Thanks
0
Martin LissOlder than dirtCommented:
Try this. It will add to the tenures and change the column heading to reflect the month name when the workbook is opened on any day of a new month. I manually changed the heading to show "February" and so when you open this workbook it will be changed to "March". It will continue to say "March" until you open it in April.
29091790.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Louise HitchcockAuthor Commented:
That's great thank you. Can you explain what this (C1048576) refers to in the VBA code? Just trying to understand it a little better.

Thanks for your help.
Louise
0
Martin LissOlder than dirtCommented:
It is the address of the last possible cell in column "C". It is used to help determine the last row with data in a column. There are several other ways to do it including
lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row

and

lngLastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

and

lngLastRow = Activesheet.UsedRange.Rows.Count

Excel isn't very good at keeping track of the last row that actually has data, and formatting of "unused" cells can fool it, so at times you may have to use one of the other calculations.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.