seconds decimal in excel time value

MalFr

New Member
Joined
Apr 10, 2015
Messages
32
Hi All,

I'm struggling to get the excel timecode of a value I "receive" as a string, so I can make further manipulation of it.
The sting is in the format " 2016/12/02 23:24:30.67", where the leading character is a space and the seconds decimal is important.
Eliminating the leading space is no problem either with trim() or mid().

But when I try and get the datevalue() or timevalue() when including the seconds decimal, I get a runtime error: Type mismatch
Code:
                TV1 = Mid(Cells(RowLoop, 4), 2, 22)
                ExpTrans = TimeValue(TV1)
ExpTrans is declared as double byte, TV1 as variant

I'm about to convert the seconds decimal to a timevalue as a separate operation and then add it to the date and time values to get the full value of the string.
But Is there any other better, or more efficient way?

Cheers,

mal
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
I don't know a specific way, and its the sort of thing where someone will know some obscure bespoke method that most people have never heard of, so for what its worth...

TV1 is a variant so Excel can decide what it actually treats it as. When you use the Text function MID, it decides its text, hence your type mismatch. This is probably exasperated by the decimal element on the end, so I'd remove that and put it back on later if needed. Given the issues with floating point errors when dealing with numbers of this size, you may need to start multiplying your original values up a bit in order to work effectively with these values

I'd probably:
- remove the decimal element
- add 0 to the result if needed to transfer it back to a number
- multiply everything up a little in order to work with whole numbers rather than numbers in Excel time format (which are all long decimals and hence subject to cutting off)
- stick the decimal back on when needed
 
Upvote 0
If there is always a leading space and always a two digit hour then

=TIMEVALUE(LEFT(A1,20)) + (RIGHT(B3,2)/24/60/60/100)

(the date information will be lost. If you want to preserve that then =DATEVALUE(LEFT(A1,20)) + TIMEVALUE(LEFT(A1,20)) + (RIGHT(A1,2)/24/60/60/100))

Will convert your string to an excel serial time. Note that getting excel to display the decimal second is a bit of a chore.


This is a case where the VBA coding is exactly the same as the worksheet formula.
 
Last edited:
Upvote 0
Thanks Baitmaster and sorry for the delay to reply.
basically this is what I've done and from a calculation point of view works fine.

as our world gets more and more precise it's surprising that excel hasn't caught up and provide better support for second decimals already.
But heh.... :)

Have a good Christmas and New year!

mal
 
Upvote 0
Hi Mike - sorry for the delay to get back and thank you.

Yes, always a leading space and always a 2 digit hour.
8640000 - a number I now know very well.... :)

As you say, getting excel to display the seconds decimal after the calculation(s) really is a chore.
I tried various solutions found on the web and none seemed to be very effective. even without the VBA using plain excel number formatting (custom) I couldn't get any decent results.
In the end I converted the date/time to a normal string and then added the decimal seconds string to the end.
what other ways are there?

Thanks for your help and have a good Christmas,

Mal
 
Upvote 0
Time is not usually expressed in hours, minutes and decimal seconds. 1/100 th of a second is a tiny fraction of a day.

Have you considered moving the whole worksheet to decimal seconds rather than excel serial time?

You get hh:mm:ss.ss, and the conversion from that to decimal seconds is pretty straight forward.

If you can abandon that odd formatting once you've converted the import, that would be the easiest way to go.

If you have to output in the same format, I'd leave it in decimal seconds format until the very last step.
 
Upvote 0
Hi All,

I'm struggling to get the excel timecode of a value I "receive" as a string, so I can make further manipulation of it.
The sting is in the format " 2016/12/02 23:24:30.67", where the leading character is a space and the seconds decimal is important.
Eliminating the leading space is no problem either with trim() or mid().

But when I try and get the datevalue() or timevalue() when including the seconds decimal, I get a runtime error: Type mismatch
Code:
                TV1 = Mid(Cells(RowLoop, 4), 2, 22)
                ExpTrans = TimeValue(TV1)
ExpTrans is declared as double byte, TV1 as variant
Here is a UDF (user defined function) that may help... it assumes the time part of the text string argument is in 24-hour format.
Code:
Function PreciseTV(ByVal S As String) As Date
  S = Trim(S)
  If InStr(S, " ") Then S = Mid(S, InStr(S, " ") + 1)
  PreciseTV = Left(S, InStr(S & ".", ".") - 1)
  If InStr(S, ".") Then PreciseTV = PreciseTV + (Mid(S, InStr(S, ".") + 1)) / 8640000
End Function
The serial time value will be correct if you involve this function in a calculation. If you simply want to return a real time value to a cell, you will have to use this Custom Format on the cell in order to see the decimal part of the seconds...

h:mm:ss.00

So, it you have this text in cell A1... " 2016/12/02 23:24:30.67" (without the quote marks, of course)... then formula in cell B1 will return a real Excel time value, and if you use the above custom format, it will display this...

23:24:30.67

By the way, the function name PreciseTV is short for "Precise Time Value".


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use PreciseTV just like it was a built-in Excel function (see above example of its use).

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Last edited:
Upvote 0
Sorry for the delay to reply, but thanks for the suggestion.
Now the initial urgency of the problem has been resolved, I need to look at a more permanant solution over the coming weeks/months.
The date/time needs to be both machine (excel) and human readable, so I think keeping it as decimal seconds won't be a solution.
However, I may well split the date and time portions but will see what the eventual users think about that idea first.

Cheers,

mal
 
Upvote 0
Thanks Rick, sorry for the delay.
I can see how that works, I may try it in a couple of weeks when I have a bit more time.
It certainly seems a little more elegant than splitting the date and time into separate columns.

Cheers,

Mal
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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