jonastiger
Member
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
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") |