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.

 

[C_Rules]

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

This works for my first post but not for my second one. I need excel to retun a value with 6 digits at the end so it would look like 2024-01-02-00.00.00.000001 instead of 2024-01-02-00.00.00.001. It's ok if excel recognizes it as either a date or text field.

 

[kvsrinivasamurthy]

It is not possible to upload in XL2BB , because I don't have 365 version. I am taking the help of internet 365 version solutions.
Try in B2

=LET(a,A2:A8,BYROW(a,LAMBDA(r,TEXT(r,"yyyy-mm-dd-hh.mm.ss")&TEXT((COUNTIF(INDEX(a,1):r,r)-1)/1000000,".000000"))))

 

[C_Rules]

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

Thanks for the welcome.

I think this one is simple and gets me close enough where i can simply add the extra characters to get the result I need. The formula you provided returns milliseconds out 3 decimal places .000 but I need 6 decimal places .000000. To get around that I copied the results into WordPad, then back to excel into column C, and in column D use =C2&"000" to get the final desired result which looks like:
2024-01-02-00.00.00.000000
2024-01-02-00.00.00.001000

If there's a formula that can return the above that would be awesome if not I'm thankful for you for leading me 99.9% of the way.