Millisecond Date Filling

C

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?
 
Sort by date Sort by votes
kvsrinivasamurthy said:
It is not possible to upload in XL2BB , because I don't have 365 version.
Click to expand...
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)?

C_Rules said:
but I need 6 decimal places .000000
Click to expand...
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
Peter_SSs said:
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.
Click to expand...

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
bebo021999 said:
Book1
AB
1202401022024-01-02.00.00.00.000000
2202401022024-01-02.00.00.00.000001
3202401022024-01-02.00.00.00.000002
4202401022024-01-02.00.00.00.000003
5202401032024-01-03.00.00.00.000000
6202401032024-01-03.00.00.00.000001
7202401032024-01-03.00.00.00.000002
Sheet1
Cell Formulas
RangeFormula
B1:B7B1=TEXT(A1,"0000-00-00.00.00.00.")&TEXT(COUNTIF($A$1:A1,A1)-1,"000000")
Click to expand...
Super close but it needs a dash after the date instead of a period you result was 2024-02-07.00.00.00.000000 I need 2024-02-07-00.00.00.000000.
 
Upvote 0
kvsrinivasamurthy said:
Same formula is available in Post#9 for 365 version.
Click to expand...
Doesn't work it returns a lot of #VALUE! and for the one cell it populates there's an extra dash right before the last 6 digits. Also I don't need it to spill.

Initial Timestamp
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
1900-01-00-00.00.00-.000001
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA Separate Day and Time to Another Column if condition meet
2
Replies
10
Views
165
SamKhem
S
C
  • Question Question
Create a new row for every date in row and create multiple copies of data to new worksheet based on cell number value for each date in the row VBA
Replies
0
Views
195
Chris258
C
K
  • Question Question
Excel VBA for Data Handling
Replies
1
Views
110
MikeVol
MikeVol
Seafox
  • Question Question
Glide Path Calculation
Replies
3
Views
394
RobP
R
C
  • Question Question
Network days from a dropdown
Replies
4
Views
135
Asbestos_Jen
Asbestos_Jen

Share this page

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