Generate Sequence using Countifs

[jonastiger]

Hi
I'm using Excel 2019. Please see the Table below.
I need to calculate a sequence number based on a date range, returning a structure like "yy0000". I did use the COUNTIFS function, but it seems that it doesn't work with decimal part number. It only consider the integer part, so when exists two equal integers, the sequence is interrupted. How do I solve this?
Thank You very much in advance
JT

	A	B	C	D	E​
	DATE	DATE (General Format)	Year	Calculated Sequence	Should Be
1	15/12/2023 02:35	45275,108	23	230001	230001
2	30/12/2023 08:10	45290,340	23	230002	230002
3	13/01/2024 05:20	45304,222	24	240001	240001
4	31/01/2024 10:00	45322,417	24	240002	240002
5	19/02/2024 05:00	45341,208	24	240003	240003
6	09/03/2024 13:21	45360,556	24	240005	240004
7	09/03/2024 23:05	45360,962	24	240005	240005
8	29/03/2024 04:35	45380,191	24	240006	240006
9	01/04/2024 00:00	45383,000	24	240007	240007
10	09/04/2024 07:27	45391,310	24	240008	240008
11	16/04/2024 08:09	45398,340	24	240009	240009
12	10/09/2024 16:17	45545,678	24	240011	240010
13	10/09/2024 21:00	45545,875	24	240011	240011
14	11/11/2024 00:39	45607,027	24	240012	240012
15	08/12/2024 09:09	45634,381	24	240013	240013
16	26/12/2024 09:00	45652,375	24	240014	240014
17	30/12/2024 06:45	45656,281	24	240015	240015
18	08/01/2025 11:31	45665,480	25	250001	250001
19	11/02/2025 00:14	45699,010	25	250002	250002
20	15/02/2025 14:28	45703,603	25	250003	250003
	Formula in D1 (Calculated Sequence):
	=C1&TEXT(COUNTIFS($C$1:$C$20;C1;$D$1:$D$20;"<"&D1+1);"0000")

 

[GraH - Guido]

This seems to work
=D2&TEXT(COUNTIFS($D$1:D2;D2;$C$1:C2;"<"&C2)+1;"0000")

 

[jonastiger]

It works very fine
Thank You very much for your help

 

[GraH - Guido]

Glad to have helped. Thanks for letting us know it's working.