# Office 365 >  > [SOLVED] Sequential numbering (2 different numbers?)

## NiesahClark

Hi, I don't know if this is even possible, but I'll try to make this as clear as I can:

In our lab, our sample numbers are in this format: yymmddPT##, where PT stands for plant (in this particular example), and the ## is the number of that sample for that day. We always have a PT01 and a PT02, at least. So the numbers from today would be 201117PT01 and 201117PT02. I want to know if there is a formula that can automatically populate these numbers based on the date entered? See attached image for what I'm looking at.

Thanks for any help in advance!!

----------


## Pete_UK

I can't see an attached image, but it would be better to attach a sample Excel workbook anyway, so that we can try out different approaches in it before getting back to you. See the yellow banner at the top of the screen for details of how to attach a workbook to one of your posts.

Pete

----------


## NiesahClark

Ok, I'm attaching it now. Sorry about that. I don't have any formulas or anything on there right now, just a layout. But hopefully you can see where I'm going with it.

----------


## Pete_UK

You can use this formula in C2:

=IF(C$1="","",TEXT(C$1,"yymmddPT")&TEXT(ROWS($1:1),"00"))

then copy across and down as required.

Hope this helps.

Pete

----------


## NiesahClark

That worked. Thanks so much!!

So I'm not a super-advanced excel user, so I apologize if this is a dumb question. But could you explain the breakdown of that formula to me?

----------


## Pete_UK

You're welcome - glad to help.

If that takes care of your original question, please take a moment to select _Thread Tools_ from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

There are basically 3 parts to the formula. The IF part just looks to see if the cell in column C is empty, and if so it returns an empty cell.

If column C is not empty, it is assumed to contain a date, and the first TEXT part of the formula just formats this date as yymmdd and adds "PT" onto the end of that.

Also added on to the end is a two digit number. The ROWS($1:1) part initially returns the value 1, and so the TEXT function around it forces it into a 2-digit number, i.e. 01. But, when the formula is copied down the ROWS term becomes ROWS($1:2), which will return 2 and this in turn becomes 02, then on the next row it becomes ROWS($1:3), thus returning 03, and so on. So this part of the formula produces an incrementing 2-digit number as it is copied down.

Hope this helps.

Pete

----------

