How to manage time in seconds in text format?
I’m reporting on one of the older telecom applications which exports the data in a text format.
I can convert the text to time.
Issue: The issue surfaces when I get a time in this format - :00, - :22,
The data is changing from :22 to 00:22:00 which changes 22 seconds to 22 minutes.
Sub FixTime() For Each cell In Range("A1:A10") cell.Value = "00" & cell.Value Next End Sub
This does not happen if I have 00:22:00 (minutes) and 01:22:00 (Hour).
I highly recommend to use the TimeSerial function to produce a real time format and not a string again. The disadvantage of using the Format function is that is produces a string again. Note that this string might look like a time but it is not. For Excel it is just a string while if you use
TimeSerial() you will get a real time that Excel recognizes as time and can calculate with.
Rule No. 1 when you are working with dates and times is: Never let Excel convert date or times on it's own. Never let Excel guess which format this string is. Never let Excel decide anything! • Instead always be specific and parse the string yourself. You are the only one who can tell for 100 % sure which format should be the correct one. Excel will definitely fail soon or later.
Option Explicit Public Sub ConvertStringIntoTimes() Dim Cell As Range For Each Cell In Range("A1:A10").Cells Dim RawData() As String 'split cell text by `:` RawData = Split(Cell.Text, ":") 'validate if one is empty string or non-numeric Dim i As Long For i = 0 To UBound(RawData) If RawData(i) = vbNullString Then RawData(i) = 0 'epmty string is considered to be zero ElseIf Not IsNumeric(RawData(i)) Then GoTo NextCell 'non-numeric cannot be handled continue with next cell End If Next i Dim OutputTime As Date Select Case UBound(RawData) Case 2: '01:22:33 or :22:33 OutputTime = TimeSerial(RawData(0), RawData(1), RawData(2)) Case 1: '22:33 or :33 OutputTime = TimeSerial(0, RawData(0), RawData(1)) Case 0: '33 OutputTime = TimeSerial(0, 0, RawData(0)) Case Else: 'anything else that connot be converted GoTo NextCell End Select 'set numberformat and write value as real time Cell.NumberFormat = "[$-F400]h:mm:ss AM/PM" 'choose your desired time format Cell.Value = OutputTime NextCell: Next Cell End Sub
Image 1: Left is the input data, right column is the output that the procedure will give.
A Beginner's Guide to the Python time Module – Real Python, The Epoch; Python Time in Seconds as a Floating Point Number; Python You learned in the previous section that you can manage Python time with a Also, notice that the format argument can consist of text other than formatting directives. Elapsed time, in seconds. If you are working with a formula that returns a time where the number of seconds exceeds 60, use a number format similar to [ss]. ss: Display the second as a number with a leading zero when appropriate.Note that if you want to display fractions of a second, use a number format similar to h:mm:ss.00. AM/PM, am/pm, A/P, a/p
Welcome to SO Maciej. I'm hoping that I used some valid examples and this code will get you started!
Sub FixTime() Dim cell As Range For Each cell In Range("A1:A10") Select Case Len(cell.Value) Case 3 'seconds cell.Offset(0, 1).Value = TimeSerial(0, 0, Right(cell.Value, 2)) Case 6 'minutes cell.Offset(0, 1).Value = TimeSerial(0, Mid(cell.Value, 2, 2), Right(cell.Value, 2)) Case 8 'full time cell.Offset(0, 1).Value = TimeSerial(Left(cell.Value, 2), Mid(cell.Value, 4, 2), Right(cell.Value, 2)) End Select Next End Sub
(the code has been adjusted using
TimeSerial as per @Pᴇʜ's comment)
I'm converting values from column A and dropping the results into column B (feel free to skip the
Offset(0,1) part to simply replace the existing values):
Using Time (Hours, Minutes, Seconds) as a Measure, I have a lot of data points that include hours, minutes, and seconds. as a date/ time, it won't let me move it to the measure column while keeping the time format. Jonathan - do you know of a way to convert the hh:mm:ss text string to a measure so I Tableau Data Management � Tableau Server Management � Ask Data� Step 1 Make sure the time is formatted properly – go to Format Cells (press Ctrl + 1) and make sure the number is set to Time. Step 2 Use the =HOUR (B2) formula to get the number of hours from the time and multiply by 3600 (there are 3600 seconds in every hour)
Try something like this.
Sub FixTime() Dim t As Double Dim s As String For Each cell In Range("A1:A10") If Left(cell, 3) = "- :" Then s = Replace(cell, "- :", "") t = Val(s) / 24 t = t / 3600 cell.Value = Format(t, "hh:mm:ss") Else cell.Value = "00" & cell.Value End If Next End Sub
How to quickly convert time to text or number of hours/minutes , How to quickly convert time to text or number of hours/minutes/seconds in Excel? To convert time to number string, you just need to format the time cells as� If you have a spreadsheet with time values that have been added to the spreadsheet as text values, you need the TIMEVALUE function. This will allow you to convert the text values into valid time values. TIMEVALUE() Syntax. The TIMEVALUE function is used to convert a text value into a time value. It has the following syntax: =TIMEVALUE(time_text)
[PDF] Using Date and Date/Time in Formulas, Date formulas are useful for managing payment deadlines, contract ages, such as CreatedDate, are Date/Time fields, meaning they not only store a NOW() function if you want the current hour, minute, seconds, or milliseconds. You can also convert text to a Date so you can use the string value with� Elapsed time in seconds is displayed. Format code "[ss].00" displays elapsed time in seconds and its fraction(s). Ex. Code "[ss].00" will display as => 1036.80: AM/PM, am/pm, A/P, a/p If these codes are included in the format, the hour is displayed using a 12-hour clock, else the hour is based on the 24-hour format.
Using Date, Date/Time, and Time Values in Formulas, Date formulas are useful for managing payment deadlines, contract ages, or any are Date/Time fields, meaning they not only store a date value, but also a time NOW() function if you want the current hour, minute, seconds, or milliseconds. You can also convert text to a Date so you can use the string value with your� The format in which the output time values will be saved. The list of output time formats depends on the output data type specified for the output time field. A custom format can also be used when the data type of the output time field is not Date. For a list of custom formats, see the explanation of Input Time Format.
For times, the process is very similar. Excel has function to retrieve the hours, minutes, and seconds from a time stamp, conveniently named HOUR, MINUTE, and SECOND. The syntax is identical: =HOUR(serial_number) =MINUTE(serial_number) =SECOND(serial_number) The serial_number in each can be any time/date-formatted cell reference.
- What's exactly the value from the system?
:22nothing in from of that? just a string
- So long as it is a text string, just test to see what it is. eg: if it has a length of 3 and the first character is a colon, then prepend
- If a cell contains
:22this is a text and not a number. You cannot calculate with it. If you turn that into a
00:22then Excel assumes
hh:mm(hours:minutes). If you wante it to be seconds you must enter
00:00:22. • So you need to adjust your code so that it adds
00:00infront of your
:22to keep it seconds.
- Yes, I understand the concept of text -> Time conversion in order to be able to calculate. The problem is that I have multiple times coming out of the system that are all in TEXT format. If I add 00:00 into the Macro then my :22 gets nicely fixed but everything else ends up in something completely ridiculous. :00:55 for example ends up with 00:00:00:55... :/
- :22 represents 22 sec of a call length. Some calls are :02:35 - 2 minutes and 35 sec. The system spits out time in a text format making it very complicated to report on.
- Thank You everyone for amazing help! Cheers!
- No problem at all Maciej. Feel free to upvote and/or accept one of the answers if that helped. See link for more info.
- @JustynaMK just a note, while this will work at first glance, it produces only a string (that might look like a time, but is again only text). I recommend to produce a real time format (that Excel can calculate with) and not letting Excel guess. It might guess wrong (especially with dates, and also on times as the original question shows). The most reliable approach is to use
TimeSerialto produce a real time.
- Very good point, thank you @Pᴇʜ ! I have adjusted the code accordingly.
- @JustynaMK well, you again used
Format(). Note that this will turn it into a string again!
Format()can only return strings! just remove
Formatcompletely and set the
.NumberFormatfor that cells to time format of your choice. This way the cell contains a real time value (that is formatted by number format). Now Excel can use that and calculate with it.
- Note that
Formathas one big disadvantage: It produces a string again. I highly recommend not to use strings when using dates or times. Only use
Format()when this is shown on a report/printout, or label in a UserForm or something. Never output a date/time as a string if this is meant to be data. You will run into issues soon or later. Also never let Excel guess.