2019: Week 19 Solution
You can find our full solution workflow below and download it here!
There are two main ways to convert the [Time] and [Gap] fields into seconds and I’ve decided to highlight both for this solution post, as well as putting both in the solution workflow (as seen above).
The functions differ slightly (highlighted above) as [Gap] has an extra ‘+’ at the start and has no gap for the leader (hence the ZN()). The good thing about this method is mainly how quick and clean it is. If you wanted you could skip the Clean steps I used and accomplish the same results using three MID() functions (as seen below). This brings down the number of changes required to just 2, however then it becomes trickier to get the formulae correct. The downside is the aforementioned requirement of knowing how long your string is in order to get the above functions correct – it may not be obvious when you’re off by a digit and lead to some peculiar results.
You can accomplish the steps below without modifying the changes, however that requires further changes and calculated fields.
This takes a little longer than the first method, however it requires less care and you can be more certain of your results.
 |
| The full solution workflow. |
There are two main ways to convert the [Time] and [Gap] fields into seconds and I’ve decided to highlight both for this solution post, as well as putting both in the solution workflow (as seen above).
Use String functions within a single calculated field
This is my preferred method, but it requires you to know how far into each field the desired numbers lie. To make this easier for myself I first used the Clean options to filter out all Letters, Punctuation, and Spaces.- Use the LEFT() function to obtain the hours, the MID() function to obtain the minutes, and the RIGHT() function to obtain the seconds.
- Use the INT() function to convert these into integers.
- Multiply the hours by 3600 (60*60) and the minutes by 60.
- Add them all up and use ZN() to convert any NULLs to zeros.
You can see the full formulas below.
[Time
(secs)]
|
[Gap
(secs)]
|
INT( LEFT([Times],2) ) * 3600
+
INT( MID([Times],3,2) ) * 60
+
INT( RIGHT([Times],2) )
|
ZN(
INT( LEFT([Gap],3) ) * 3600
+
INT( MID([Gap],4,2) ) * 60
+
INT( RIGHT([Gap],2) )
)
|
The functions differ slightly (highlighted above) as [Gap] has an extra ‘+’ at the start and has no gap for the leader (hence the ZN()). The good thing about this method is mainly how quick and clean it is. If you wanted you could skip the Clean steps I used and accomplish the same results using three MID() functions (as seen below). This brings down the number of changes required to just 2, however then it becomes trickier to get the formulae correct. The downside is the aforementioned requirement of knowing how long your string is in order to get the above functions correct – it may not be obvious when you’re off by a digit and lead to some peculiar results.
Use a Custom Split and then modify the generated fields.
This method also works well and highlights a little trick you may not be aware of: you can open up and modify the ‘Changes’ produced when using one of the Split options as seen below. |
| Splitting [Time] and Modifying a Change |
You can accomplish the steps below without modifying the changes, however that requires further changes and calculated fields.
- Split the [Time] field on every space the [Gap] field on Last 3 spaces (we don’t need the + at the start).
- On each of the Changes produced, double click, click the prencil icon, or right-click & select Edit.
- Trim away the extra letters/symbols by wrapping the function in each of them inside a LEFT() function to just take the left 2 character.
- Also rename the fields, replacing “Split X” with ‘Hours’, ‘Mins’, or ‘Secs’ where appropriate.
- Create new calculated fields for Time and Gap where you using steps 2-4 of the previous method.
This takes a little longer than the first method, however it requires less care and you can be more certain of your results.
