Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Mar 2016
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    help in unique sequence numbering

    hi,

    I'm trying to get this specific numbering sequence using the drag function
    0001/04/2016
    0002/04/2016
    0003/04/2016

    i tried using the date format cells function but once it goes to the next month, the month change

    ty

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Welcome to the Lounge.

    What should it be after you drag past: 0030/04/2016 ? Should it be: 0001/05/2016 ?

    If so, and if you put the starting date in the first row (as 4/1/2016),
    this should work: ="00"&TEXT(DATEVALUE("4/1/2016")+ROW()-1,"dd/mm/yyyy")
    Last edited by kweaver; 2016-03-31 at 16:07.

  3. #3
    New Lounger
    Join Date
    Mar 2016
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    yup, after 0030/04/2016, it changed to 0001/05/2016,

    i want it to continue with 0031/04/2016 and so on, using it to refer order for April 2016

  4. #4
    New Lounger
    Join Date
    Mar 2016
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    btw kweaver, thanks for the reply

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    April only has 30 days. Or, does this mean the 31st entry for April? My formula considers the number of days in the month.

    This formula keeps the numbering for April: =TEXT(ROW(),"0000")&"/04/2016"
    Last edited by kweaver; 2016-03-31 at 16:42.

  6. The Following User Says Thank You to kweaver For This Useful Post:

    tatanasa (2016-03-31)

  7. #6
    New Lounger
    Join Date
    Mar 2016
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks for the fast reply, finally the number keep running past 30 during April

    thanks a lot

  8. #7
    New Lounger
    Join Date
    Mar 2016
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    one more thing, how do i minus 1 in the starting number since the first row used for title?

  9. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    If I understand you correctly...

    My formula was for April: =TEXT(ROW(),"0000")&"/04/2016"
    And, this assumes that you are starting in row 1 of the sheet.

    With a title in row 1, I assume your count begins in row 2.
    Therefore: =TEXT(ROW()-1,"0000")&"/04/2016"

    If you're starting with row 4 for example, then change the formula to be: =TEXT(ROW()-3,"0000")&"/04/2016"

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    tatanasa,

    Here is a user defined function (UDF) that will increment the first section while keeping the month/year from changing

    Place in a standard module
    Code:
    Public Function Increment(rng As Range, dte As String)
        Increment = Right("0000" & Left(rng, 4) + 1, 4) & "/" & dte
    End Function
    In cell A2, place the string 0001/4/2016
    in cell A3, place the formula =Increment(A2,"04/2016") then copy down.

    The column should be formatted as General

    HTH,
    Maud

    increment.png

  11. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    As an alternative to a UDF, in cell A2 place the initial string then in A3, place the formula:

    =RIGHT("0000" & LEFT(B2,4)+1,4) & RIGHT(B2,8) then copy down

    HTH,
    Maud

  12. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    ..another way would be to just use a custom number format for the initial cell..
    0000"/04/2016"

    zeddy

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
  •