Millisecond Date Filling

C_Rules

New Member
Joined
Feb 21, 2025
Messages
5
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?
 
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
ABC
1
2202401022024-01-02.0000002024-01-02.000000
3202401022024-01-02.0000012024-01-02.000001
4202401022024-01-02.0000022024-01-02.000002
5202401022024-01-02.0000032024-01-02.000003
6202401032024-01-03.0000002024-01-03.000000
7202401032024-01-03.0000012024-01-03.000001
8202401032024-01-03.0000022024-01-03.000002
C_Rules (4)
Cell Formulas
RangeFormula
B2:B8B2=MAP(A2:A8,LAMBDA(r,TEXT(r,"0000-00-00.")&TEXT(COUNTIF(A$2:r,r)-1,"000000")))
C2:C8C2=TEXT(A2,"0000-00-00.")&TEXT(COUNTIF(A$2:A2,A2)-1,"000000")
Dynamic array formulas.
 
Upvote 0
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
ABC
1
2202401022024-01-02.0000002024-01-02.000000
3202401022024-01-02.0000012024-01-02.000001
4202401022024-01-02.0000022024-01-02.000002
5202401022024-01-02.0000032024-01-02.000003
6202401032024-01-03.0000002024-01-03.000000
7202401032024-01-03.0000012024-01-03.000001
8202401032024-01-03.0000022024-01-03.000002
C_Rules (4)
Cell Formulas
RangeFormula
B2:B8B2=MAP(A2:A8,LAMBDA(r,TEXT(r,"0000-00-00.")&TEXT(COUNTIF(A$2:r,r)-1,"000000")))
C2:C8C2=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.
 
Upvote 0

Forum statistics

Threads
1,226,882
Messages
6,193,481
Members
453,803
Latest member
hbvba

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