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?

 

[Peter_SSs]

It is not possible to upload in XL2BB , because I don't have 365 version.

XL2BB does not require 365 version, unless you are using a Mac (which your profile doesn't give information about). Why not update your profile so we know your version(s)/platform(s)?

but I need 6 decimal places .000000

Sorry, I missed the extra digits. In that case I think the results will need to be text and not actual dates/times (numerical).

Depending on whether you want a spilling formula or one copied down, see if this is better.

25 02 22.xlsm
	A	B	C
1
2	20240102	2024-01-02.000000	2024-01-02.000000
3	20240102	2024-01-02.000001	2024-01-02.000001
4	20240102	2024-01-02.000002	2024-01-02.000002
5	20240102	2024-01-02.000003	2024-01-02.000003
6	20240103	2024-01-03.000000	2024-01-03.000000
7	20240103	2024-01-03.000001	2024-01-03.000001
8	20240103	2024-01-03.000002	2024-01-03.000002
C_Rules (4)

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

 

[kvsrinivasamurthy]

I am having 2019 version only.

 

[Peter_SSs]

I am having 2019 version only.

XL2BB works on 2019 ....

... unless you are using a Mac (which your profile doesn't give information about). Why not update your profile so we know your version(s)/platform(s)?

 

[C_Rules]

Sorry, I missed the extra digits. In that case I think the results will need to be text and not actual dates/times (numerical).

Depending on whether you want a spilling formula or one copied down, see if this is better.

25 02 22.xlsm
	A	B	C
1
2	20240102	2024-01-02.000000	2024-01-02.000000
3	20240102	2024-01-02.000001	2024-01-02.000001
4	20240102	2024-01-02.000002	2024-01-02.000002
5	20240102	2024-01-02.000003	2024-01-02.000003
6	20240103	2024-01-03.000000	2024-01-03.000000
7	20240103	2024-01-03.000001	2024-01-03.000001
8	20240103	2024-01-03.000002	2024-01-03.000002
C_Rules (4)

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

Final result with your formula is 2024-02-07.000000 it would need to return 2024-02-07-00.00.00.000000 which is year, month, day, hours, minutes, seconds, milliseconds (out six decimal places) to be spot on.

 

[bebo021999]

Book1
	A	B
1	20240102	2024-01-02.00.00.00.000000
2	20240102	2024-01-02.00.00.00.000001
3	20240102	2024-01-02.00.00.00.000002
4	20240102	2024-01-02.00.00.00.000003
5	20240103	2024-01-03.00.00.00.000000
6	20240103	2024-01-03.00.00.00.000001
7	20240103	2024-01-03.00.00.00.000002
Sheet1

Cell Formulas
Range	Formula
B1:B7	B1	=TEXT(A1,"0000-00-00.00.00.00.")&TEXT(COUNTIF($A$1:A1,A1)-1,"000000")