Generate Serial number based on date

**Jamesdlm** · 02-09-2015, 06:14 PM

I am trying to have excel generate a serial number based on dates. In column a I enter the date (usually today's date) and want column B to generate a simple serial number yyJJJ.01 with the decimal increasing with each instance of the same date. I have a formula working for everything in front of the decimal but can't get the decimal to change unless I enter it manually (which I have been doing) here is the formula I am using =RIGHT(YEAR(A2),2)&TEXT(A2-DATE(YEAR(A2),1,0),"000.01"), any help is appreciated

**daffodil11** · 02-09-2015, 06:25 PM

Maybe B2:

Please Login or Register  to view this content.

and drag down?

**Jamesdlm** · 02-09-2015, 06:52 PM

Thanks but this changes the number before the decimal and in random amounts.
09-Feb-2015 15040.01
09-Feb-2015 13172.01
09-Feb-2015 13214.01
09-Feb-2015 13254.01
09-Feb-2015 14050.01
09-Feb-2015 14052.01
09-Feb-2015 14092.01
The first in the string is what the formula I am using generates, when I posted the suggestion are the next 6 in the string

**daffodil11** · 02-09-2015, 07:03 PM

Wrong absolute reference.

Change:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**Jamesdlm** · 02-10-2015, 09:05 AM

Beautiful! Thank you,, your help is much appreciated.

**daddylonglegs** · 02-10-2015, 09:41 AM

Wouldn't it be simpler to count the dates in column A for the decimal part?

Using that approach you could use this formula which returns a number

=TEXT(A2,"yy")*1000+A2-DATE(YEAR(A2),1,0)+COUNTIF(A$2:A2,A2)/100

format as number

**SAMMY101** · 10-16-2019, 03:12 PM

Hi..I'm just new here and I fascinated how you were discussing the serial number generation.

I was wondering how can I make serial number from a given date and reflect it on the adjacent cell as QYYMM.RR, where Q is only reference leter, YY is the year (in 2 digit format), MM (in 2 digit format) is the month and RR is the running number in decimal that increases if it the previous value in a column is the same date while it restarted to .01 when it is new date?

example:

September 30, 2019 Q1909.01
October 01, 2019 Q1910.01
October 01, 2019 Q1910.02

**FDibbins** · 10-17-2019, 12:32 AM

Originally Posted by SAMMY101

Hi..I'm just new here and I fascinated how you were discussing the serial number generation.

I was wondering how can I make serial number from a given date and reflect it on the adjacent cell as QYYMM.RR, where Q is only reference leter, YY is the year (in 2 digit format), MM (in 2 digit format) is the month and RR is the running number in decimal that increases if it the previous value in a column is the same date while it restarted to .01 when it is new date?

example:

September 30, 2019 Q1909.01
October 01, 2019 Q1910.01
October 01, 2019 Q1910.02

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

**SAMMY101** · 10-23-2019, 08:33 AM

Admin,

I see. Okay. Perhaps I'll have to generate new inquiry as you said.
Thanks for the reminder.

Sam

Generate Serial number based on date

LinkBack

Thread Tools

Rate This Thread

Display

Generate Serial number based on date

Re: Generate Serial number based on date

Re: Generate Serial number based on date

Re: Generate Serial number based on date

Re: Generate Serial number based on date

Re: Generate Serial number based on date

Re: Generate Serial number based on date

Re: Generate Serial number based on date

Re: Generate Serial number based on date

Thread Information

Users Browsing this Thread

Similar Threads

Auto generate serial number

Auto generate serial number

code to generate serial number based on number of records

how to generate serial number on a textbox in userform

Need macro/script to generate serial number labels

Bookmarks

Bookmarks

Posting Permissions