I have a system generated data where the time format includes milliseconds , example 08/10/2023 11:35:02:286. I need only the time. I want to create two columns with start and end times with a third column calculating the difference (in seconds). So far I used 'text to column' to separate the time from the date, however when i tried to calculate the difference using a simple minus operation it returned #VALUE. Any ideas on how I could get this to work would be much appreciated. Many thanks.
Please replace in the time the last ":" with ".". So: 08/10/2023 11:35:02.286
If that does not work, please upload a sample workbook (via Go Advanced and Manange Attachments) with the error.
Last edited by HansDouwe; 10-10-2023 at 09:21 AM.
Thanks HansDouwe. . Just tried a sample 12:28:48.500 - 12:28:48.487 = 1.50463E-07. This is much improved for me. However, how do I reduce the result to at lest 2 decimal places? I'm also thinking how simple is it to add "." between the second and millisecond for all the times in an array.
I multiplied the results by 86400 that return 0.013 secs. which more like the result I'm after. Left with finding a way to add "." between the second and millisecond for all the times in an array.
Please upload a sample workbook (via Go Advanced and Manange Attachments) with manually added the expected results of the formula.
Sample worksheet attached. Thanks
The SUBSTITUTE() function should be able to handle that task. =SUBSTITUTE(date_time_text,":",".",3). Carefully note the use of the 3 in the optional 4th argument that tells SUBSTITUTE() which instance of the old_text to replace. (SUBSTITUTE() help file describing the behavior of the optional 4th argument: https://support.microsoft.com/en-us/...rs=en-us&ad=us ).
Another observation: The value of 1.5E-7 value is correct in units of days elapsed. You can use number formatting like [mm]:ss.000 or [s].000 or whatever number format code makes sense to you to display as elapsed seconds or elapsed minutes:seconds or whatever, while leaving the underlying cell value as decimal days. When you multiply by 86400, you are converting days to decimal seconds, but you can no longer use Excel's built in sexagesimal "hour:minute:second" time/number formatting. This decision is entirely up to you.
Originally Posted by shg
In your sample file, I:
1) convert time stamp text to date/time serial number. In O4, I enter =VALUE(SUBSTITUTE(B4,":",".",3)) [copy/paste/fill into O4:P8].
2) time difference is a simple subtraction . In Q4 =P4-O4 formatted as [s].000 [copy/paste/fill into Q4:Q8]
3) If I want to convert to an actual decimal seconds value, I convert the "days" value to decimal seconds. =CONVERT(Q4,"day","sec") or =Q4*86400. I find that the CONVERT() function helps me remember what I am doing, so I find it easier in the long run to use.
Here are 2 different solutions without changing the format of time start and time end.
Solution 1: Please try and copy down and format cells as a number with 2 decimals:Formula:
Please Login or Register to view this content.
Solution 2: Please try and copy down and custom format the cells as [s].00:Formula:Please Login or Register to view this content.
Last edited by HansDouwe; 10-10-2023 at 11:55 AM.
Awesome!!! Thanks Both. Really made my day. You've provided me with several brilliant solutions. Thanks a lot..
This formula takes care of days and Time up to milliseconds.
In D4 copied down.
Formula:Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Many thanks for your solution. Much appreciated.How do I amend your formula if I want to display the time difference in seconds/minute/Hour number or decimal?
Please show an example of what exactly it should look like?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks