# Thread: Increment number when Date changes

1. ## Increment number when Date changes

Good day,

I have a small problem. I have two cells:

B35 is a date cell with mmm-yy
and
B37 is a number with no decimal places.

I would like a formula that increments B37 by 1 when ever the date changes, i.e
B35: Mar-13
B37: 4
when B35 is inputted as Apr-13, then B37 changes to 5 automatically.

I hope I was able to put my problem through clearly.
Thank You.

2. If you just want to show the month's number, regardless of the year, the formula you need in B37 is simply MONTH(B35), and format B37 appropriately - the General format will do it, or use a Number format with the number of decimal places set to zero.

That formula will re-start at 1 in January 2014. Is that what you wanted, or did you want it to progress to 13 ?

3. ## The Following User Says Thank You to MartinM For This Useful Post:

frahmed99 (2013-04-24)

4. Hi,

Thanks for the reply. The number in B37 could be any number. It could be 1000. So when the user changes the date to the following month then the previous number changes to 1001. It is not showing the months number. And I would also like to have it continue counting till the following year.

5. You'd need VBA code - is that acceptable?

6. Thats fine. I will attach images to make it easy.
Sheet 1 has: Book_1.png.
When the date in B35 changes the value in B37 changes. In addition to that I have another worksheet where the columns shift when the same date changes.
Workbook_3.png
i.e User changes date in Sheet 1, then the whole Total to date column moves to the Previous column.

I m not an Excel person. However, my employers would like me to sort it out.

7. If you want to make it easy, a workbook would be much more helpful than a picture.

For part 1, right-click the sheet tab, choose View Code then paste this in:
Code:
```Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B35")) Is Nothing Then
Application.EnableEvents = False
Range("B37").Value = Range("B37").Value + 1
Application.EnableEvents = True
End If
End Sub```

8. I really apologize for not posting the whole worksheet. Its just that I was thinking of security reasons, I didn't put in the whole worksheet. The first part worked brilliantly.
Thank You.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•