• Increment number when Date changes


    Good day,

    I have a small problem. I have two cells:

    B35 is a date cell with mmm-yy
    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.

    Viewing 3 reply threads
    • #1389176

      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 ?

      • #1389182


        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.

    • #1389184

      You’d need VBA code – is that acceptable?

      • #1389187

        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.
        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.

    • #1389188

      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:

      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
      • #1389190

        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.

    • #2466391

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

      Thank you

    Viewing 3 reply threads
    Reply To: Increment number when Date changes

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: