Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Generate Series Number

0

How can i generate series base on this Format

0000-00-0000

0000 = series number

00 = Month

0000 = year

Answer
Discuss

Discussion

R

Please state how the nunber portion should change, the year range and quantity of numbers per month 
John_Ru (rep: 6092) Jun 7, '22 at 1:31 am
R

I gave you an answer anyway (based on the serial number incrementing each month) 
John_Ru (rep: 6092) Jun 7, '22 at 11:50 am
Add to Discussion

Answers

0
Selected Answer

R

A long-winded way to create a serial number per month is to put this "seed value" in cell F2 (assuming F1 is your header):

'0001-01-2021

then put this in F3:

=TEXT(VALUE(LEFT(F2,4))+1,"0000-") & IF(MOD(ROW()-1,13)=0,"01-"&RIGHT(F2,4)+1, TEXT(MOD(ROW()-1,13),"00") & "-" & RIGHT(F2,4))

(or change F2 in bold to suit). The combination of IF with MOD function (where the divisor is 13) means every 13th month, the middle "00" (within 0000-00-0000) will reset to 01 and the year will go up by one. Otherwise, the serial number goes up one, as does the month.

Copy this down and you'll get a series like:

Serial number
0001-01-2021
0002-02-2021
0003-03-2021
0004-04-2021
0005-05-2021
0006-06-2021
0007-07-2021
0008-08-2021
0009-09-2021
0010-10-2021
0011-11-2021
0012-12-2021
0013-01-2022
0014-01-2022
0015-02-2022
0016-03-2022
0017-04-2022
0018-05-2022
0019-06-2022
0020-07-2022
0021-08-2022
0022-09-2022
0023-10-2022
0024-11-2022
0025-12-2022
0026-01-2023
0027-01-2023

If your seed valiue doesn't have 01 in the middle, you will need to change the bold bit of ROW()-1 e.g. if the seed is 0001-05-2022 say, then the formula in F2 will need to have ROW()+3 in two places.

If you have Excel 365, you could look at Don's video tutorial here SEQUENCE() Function for Power List Making in Excel 365 and try to create something more sophisticated.

Hope this helps.

Discuss

Discussion

R

Thanks for selecting my Answer
John_Ru (rep: 6092) Jun 8, '22 at 4:22 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login