# Thread: help in unique sequence numbering

1. ## 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. 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")

3. 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. btw kweaver, thanks for the reply

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

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

tatanasa (2016-03-31)

7. thanks for the fast reply, finally the number keep running past 30 during April

thanks a lot

8. one more thing, how do i minus 1 in the starting number since the first row used for title?

9. 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. 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. 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. ..another way would be to just use a custom number format for the initial cell..
0000"/04/2016"

zeddy