Sign in or start a free trial to avail of this feature.
8. Converting Serial Numbers to Time Part 2
In this lesson, we learn how to use the date and time tool to convert serial numbers into standard date format.
Converting Serial Numbers to Time
- Second values that are rounded up to 60 need to be adjusted so that they read 0 instead. This can be accomplished with an IIFconditional expression
- The IIF conditional expression requires a Boolean input, or a true/false statement
Formatting Time Data
- The PadLeft formula adds a specific character to the beginning or a string entry. This helps us ensure that all time entries have two digits as required by Alteryx
In the previous lesson, we started the process of converting a serial number into a time format that's readable by Alteryx.
We managed to write formulas to derive time data for both hours and minutes as well as a formula to round seconds to the nearest integer.
However, it's possible that we could round 59.5 seconds or more to 60 seconds.
This should obviously read 0, so we'll need to add a conditional formula.
We'll start by highlighting the current round formula and pressing CTRL+X to cut it out of the formula canvas.
We'll now click on the FX icon, click the conditional group and choose IIF.
As we mentioned in the previous lesson, this formula can only be used for Booleans or true false statements.
We'll use this formula to write a true false statement that will determine a course of action if our second entries are rounded up to 60.
We'll press CTRL+V to paste the round formula over the Boole variable.
We want to guard against the formula returning 60, so we'll write equals 60 after the round formula.
Next, the X variable is the condition if the statement is true.
If the formula returns 60, we wish to output 0 so we'll replace the X with a 0.
Finally, the Y variable deals with a false output, or if the round formula returns a number other than 60.
In this case, if the formula does not equal 60 we simply want to return the number.
So we'll highlight the Y and paste in our round formula again.
We now need to bring these hours minutes and seconds together into a valid field.
At this point, it may seem like we should be able to just combine these fields with a plus sign.
However, Alteryx requires all time units to have 2 digits and our current fields will default to 1 digit for any value below 10.
We'll need to add a leading zero in these cases which we can do with the pad left formula.
We'll create another new formula and name the output time string.
We'll click on the FX icon, click on the string group and choose pad left.
The first thing we notice is that this formula requires a string, but our hour and minute data is in a numeric format.
We'll need to convert it using the tostring formula.
We'll highlight the variables of this tostring formula, click the X icon, click new fields and select hour.
We want this formula to always return two characters so we'll replace the len variable with a number 2.
Finally, when the hour is not 2 characters long, we want a leading zero.
So we'll end the formula by replacing the char variable with 0 in quotes.
We must now repeat this exercise for minutes and second.
Joining each of these formulas together using the plus sign and the colon in quotes.
Note that the second data is already a string data type.
So, no need to add the ToString formula.
Let's make sure everything is working okay by running the workflow and viewing the output in the results window.
We can see that our time string is now yiedling hours minutes and seconds.
It only remains for us to bring this together with the date component of the serial number.
We can easily do this using the serial number_out field that we created several lessons ago.
Before we do this, we'll need to separate the date values from the blank time values in that field.
We'll add a text to column icon to the workflow and ensure that the serial number_out column is selected as our field to split.
The separator is just a space. So we'll enter that in the delimiters field, change the number of columns to 2 and run the tool.
Now we can bring a formula tool onto the canvas and name the column date and time_ final.
We'll bring in the field labeled 1 which houses our date data along with the time string field.
We'll separate them with plus signs and a space.
As our last step, we'll connect one more datetime tool.
We'll specify the date and time final field as a field to convert and select the yyyy-mm-dd format followed by hours minutes and seconds.
We'll now run the tool.
As we can see, our serial number has finally been converted to the correct format.
Looking back, this might seem like a complicated process to fix a very specific issue.
However, given the ubiquity of Excel, it's very likely that you will come across serial number date data at some point.
So understanding how to convert this data is useful.
More importantly, this process should give you a flavor of how flexible the formula tool is and an another example of how data flows through Alteryx.