Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Apr 2013
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Wink 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. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    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. #3
    New Lounger
    Join Date
    Apr 2013
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You'd need VBA code - is that acceptable?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #5
    New Lounger
    Join Date
    Apr 2013
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

    Thanks in advance.

  7. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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
    Last edited by rory; 2013-04-24 at 09:25.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #7
    New Lounger
    Join Date
    Apr 2013
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

Tags for this Thread

Posting Permissions

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