Millisecond Date Filling

[C_Rules]

Hi All if I have a date range expressed as yyyy-mm-dd-hh.mm.ss.000 and I want to increase the value by one millisecond for each new row and then have the time reset back to 0 for each new day For example:
2024-01-02-00.00.00.000
2024-01-02-00.00.00.000
2024-01-02-00.00.00.000
2024-01-02-00.00.00.000
2024-01-03-00.00.00.000
2024-01-03-00.00.00.000
2024-01-03-00.00.00.000

I need a formula to change those dates in a new column to be

2024-01-02-00.00.00.000
2024-01-02-00.00.00.001
2024-01-02-00.00.00.002
2024-01-02-00.00.00.003
2024-01-03-00.00.00.000
2024-01-03-00.00.00.001
2024-01-03-00.00.00.002

How can i accomplish this?

 

[C_Rules]

Sorry have to change the above slightly. The output has to be in this specific format 2024-01-02-00.00.00.000000. So I need values that currently exist as:

20240102
20240102
20240102
20240102
20240103
20240103
20240103

to be changed to

2024-01-02-00.00.00.000000
2024-01-02-00.00.00.000001
2024-01-02-00.00.00.000002
2024-01-02-00.00.00.000003
2024-01-03-00.00.00.000000
2024-01-03-00.00.00.000001
2024-01-03-00.00.00.000002

 

[Peter_SSs]

Welcome to the MrExcel board!

I am unsure which are months and which are days in your data, but does this do what you want?

25 02 22.xlsm
	A	B
1
2	20240102	2024-01-02-00.00.00.000
3	20240102	2024-01-02-00.00.00.001
4	20240102	2024-01-02-00.00.00.002
5	20240102	2024-01-02-00.00.00.003
6	20240103	2024-01-03-00.00.00.000
7	20240103	2024-01-03-00.00.00.001
8	20240103	2024-01-03-00.00.00.002
C_Rules

Cell Formulas
Range	Formula
B2:B8	B2	=TEXT(A2,"0000-00-00")+(COUNTIF(A$2:A2,A2)-1)/86400000

 

[kvsrinivasamurthy]

Given data in A2:A8. Then In B2

=LET(a,A2:A8,BYROW(a,LAMBDA(r,r+TIMEVALUE("0:0:0.001")*(COUNTIF(INDEX(a,1):r,r)-1))))

 

[Peter_SSs]

Then In B2

Did you test that given the extra information in post #2?

For a spill version of my earlier suggestion ..

25 02 22.xlsm
	A	B
1
2	20240102	2024-01-02-00.00.00.000
3	20240102	2024-01-02-00.00.00.001
4	20240102	2024-01-02-00.00.00.002
5	20240102	2024-01-02-00.00.00.003
6	20240103	2024-01-03-00.00.00.000
7	20240103	2024-01-03-00.00.00.001
8	20240103	2024-01-03-00.00.00.002
C_Rules (2)

Cell Formulas
Range	Formula
B2:B8	B2	=MAP(A2:A8,LAMBDA(r,TEXT(r,"0000-00-00")+(COUNTIF(A$2:r,r)-1)/86400000))
Dynamic array formulas.

 

[kvsrinivasamurthy]

For Post #2, In B2

=LET(a,A2:A8,BYROW(a,LAMBDA(r,r+(1/(24*60*60*100000))*(COUNTIF(INDEX(a,1):r,r)-1))))

 

[Peter_SSs]

For Post #2, In B2

Perhaps I am not understanding the data, but this is what that gives for me. Can you post your worksheet with XL2BB?

25 02 22.xlsm
	A	B
1
2	20240102	########################################
3	20240102	########################################
4	20240102	########################################
5	20240102	########################################
6	20240103	########################################
7	20240103	########################################
8	20240103	########################################
C_Rules (3)

Cell Formulas
Range	Formula
B2:B8	B2	=LET(a,A2:A8,BYROW(a,LAMBDA(r,r+(1/(24*60*60*100000))*(COUNTIF(INDEX(a,1):r,r)-1))))
Dynamic array formulas.