Two simultaneous increments; numerically and by month.

[Bortas]

Hello there!

My organization tracks a number of different items via excel, and sorts them by number of incidents per month. I'm trying to build an variable item number, next to a column of timestamps (making use of the NOW()) function.
It might look a bit like this, as an example
5/09/2020 MAY001
5/13/2020 MAY002
5/19/2020 MAY003
5/30/2020 MAY004
6/1/2020 JUN001
6/23/2020 JUN002
7/6/2020 JUL001
7/14/2020 JUL002

I hope the above pattern helps clue my goal. In building the latter number (MAY001), I'm struggling on two fronts. First of all, the incrementation:

=TEXT(NOW(),"mmm") & "001"

I need to figure out how to change & "001" into a formula that increments on each new row. Then, that increment must reset again at the end/beginning of the month, and a new prefix substituted in.

Any ideas on how to proceed? Thanks for any assistance!

 

[AhoyNC]

Try

Book1
	A	B
1	Date	Number
2	5/9/2020	May001
3	5/13/2020	May002
4	5/19/2020	May003
5	5/30/2020	May004
6	6/1/2020	Jun001
7	6/23/2020	Jun002
8	7/6/2020	Jul001
9	7/14/2020	Jul002
10	7/20/2020	Jul003
Sheet2

Cell Formulas
Range	Formula
B2:B10	B2	=TEXT(A2,"mmm")&TEXT(SUMPRODUCT(--(MONTH($A$2:A2)=MONTH(A2))),"000")

 

[Bortas]

Brilliant! there is some nesting that's happening that I don't understand, but its working very well!

I've noted that if the user inadvertantly deletes a cell in the A column, ie A6, then all cells beyond become broken. I suspect that is centered right here: MONTH($A$2:A2) Is there a way to restate that function so that it is more resilient to empty data?

Thanks for your assistance!

 

[AhoyNC]

Not sure if this is what you want. If someone deletes a cell in column A then it will return a blank in column B.

Book1
	A	B
1	Date	Number
2	5/9/2020	May001
3
4	5/19/2020	May002
5	5/30/2020	May003
6
7	6/23/2020	Jun001
8	7/6/2020	Jul001
9	7/14/2020	Jul002
10	7/20/2020	Jul003
Sheet3

Cell Formulas
Range	Formula
B2:B10	B2	=IF(A2="","",TEXT(A2,"mmm")&TEXT(SUMPRODUCT(--(MONTH($A$2:A2)=MONTH(A2))),"000"))

 

[Bortas]

There it is! I kept writing almost that exact formula and getting it wrong - thank you for the assist!

 

[AhoyNC]

You're welcome. Thanks for the feedback.

 

[Bortas]

(wrong thread, apologies)