Millisecond Date Filling

C_Rules

New Member
Joined
Feb 21, 2025
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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?
 
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
 
Upvote 0
Upvote 0
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
AB
1
2202401022024-01-02-00.00.00.000
3202401022024-01-02-00.00.00.001
4202401022024-01-02-00.00.00.002
5202401022024-01-02-00.00.00.003
6202401032024-01-03-00.00.00.000
7202401032024-01-03-00.00.00.001
8202401032024-01-03-00.00.00.002
C_Rules (2)
Cell Formulas
RangeFormula
B2:B8B2=MAP(A2:A8,LAMBDA(r,TEXT(r,"0000-00-00")+(COUNTIF(A$2:r,r)-1)/86400000))
Dynamic array formulas.
 
Upvote 0
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
AB
1
220240102########################################
320240102########################################
420240102########################################
520240102########################################
620240103########################################
720240103########################################
820240103########################################
C_Rules (3)
Cell Formulas
RangeFormula
B2:B8B2=LET(a,A2:A8,BYROW(a,LAMBDA(r,r+(1/(24*60*60*100000))*(COUNTIF(INDEX(a,1):r,r)-1))))
Dynamic array formulas.
 
Upvote 0
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?

Cell Formulas
RangeFormula
B2:B8B2=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.
 
Upvote 0
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
Excel Formula:
=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"))))
 
Upvote 0
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?

Cell Formulas
RangeFormula
B2:B8B2=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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top