Get Time from Timestamp Data

[Steve001]

Hello Everyone,

There is loads of time related subjects on here when i searched, but trying to find what i wanted and one that worked was difficult.

I have data from a Data logger that timestamps data example as below

12/03/2024 15:49:13:179

to

15/03/2024 07:40:43:179

What i want to do

Get the total run time

Days
Hours
Minutes
Seconds

Please put comments in any example code so i can understand whats happening and why

Regards

Steve

 

[awoohaw]

well, i though i could use a custom format for total days like [dd] but that did not seem to work. So this is a text calculation:
(it does not show milliseconds)

Mr Excel Questions 75.xlsm
	A	B	C
1	12/03/2024 15:49:13:179	2024-03-12 15:49:13
2	15/03/2024 07:40:43:179	2024-03-15 07:40:43	2:15:51:30
Sheet6

Cell Formulas
Range	Formula
B1:B2	B1	=DATE(TEXTAFTER(TEXTBEFORE(A1," "),"/",2),TEXTAFTER(TEXTBEFORE(A1,"/",2),"/"),TEXTBEFORE(A1,"/"))+TIMEVALUE(LEFT(TEXTAFTER(A1," "),8))
C2	C2	=INT(B2-B1)&":"&TEXT(B2-B1,"hh:mm:ss")

 

[Steve001]

Hi Awoohaw

Is there a way of doing this in VBA ?

Steve

 

[awoohaw]

what do you mean? A custom function?

 

[Steve001]

yes please if it is possible.
I will try the above in my sheet on Monday as it didn't work at home on my excel version

Regards

Steve

 

[Peter_SSs]

it didn't work at home on my excel version

Speaking of Excel version(s) I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Dave Patton]

What result do you expect?
Custom format to your preference.

T240203a.xlsm
	A	B
1	12/03/2024 15:49:13:179	12-Mar-2024 15:49:13
2	15/03/2024 07:40:43:179	15-Mar-2024 7:40:43
3		2.660763889
4		2 15:51:30
5		63 h 51 m 30 s
6
2a

Cell Formulas
Range	Formula
B1:B2	B1	=LEFT(A1,10)+SUBSTITUTE(RIGHT(A1,12),":",".",3)
B3	B3	=B2-B1
B4	B4	=B3
B5	B5	=B3

 

[Steve001]

I would have preferred to do this behind the sheet as i have macros doing other things in there

I can used a Cell out of the way and get the data that i need

@ Peter thank you - sorry i just tried this at home for now at work we are on office 365

@Dave thank you i will do this

Regards

Steve

 

[Dave Patton]

Thanks for the feedback.

If you are not concerned with the milliseconds, try the following

T240203a.xlsm
	A		C
1	12/03/2024 15:49:13:179		2 15:51:30
2	15/03/2024 07:40:43:179
3
2a

Cell Formulas
Range	Formula
C1	C1	=LEFT(A2,19)-LEFT(A1,19)

 

[Steve001]

Hi Dave,

Can i ask in your example above how did you format the cells ?
I can get the Date to show or the time but not both as your example

Regards

Steve