Increment number when Date changes

Topic

New Reply

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
[/B]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.

Reply | Quote

Replies

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 ?

Reply | Quote

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.

Thanks in advance.

Reply | Quote

You’d need VBA code – is that acceptable?

Reply | Quote

Thats fine. I will attach images to make it easy.
Sheet 1 has: 33815-Book_1.
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.
33816-Workbook_3
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.

Thanks in advance.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

How can this apply to a range of rows and columns?

Thank you

Reply | Quote

This thread is nine years old. The posters are no longer available. Please create a new Topic in the appropriate Forum for current help. Include a link to this Topic for reference.

1 user thanked author for this post.

Microfix

Reply | Quote