 |
||
| TheExcelAddict.com | ||
| May 15, 2019 | ||
| Hi fellow Excel Addict, | ||
| As
you can see, I'm still working on my 'non-Excel'
project. From Monday to Friday I have to drive an
hour outside of town to get there. That's why my
newsletters have been coming so irregularly for the
past couple of months. Also, in the past two weeks I have lost my helper so I am working on my own now. But, after three months things are finally beginning to take shape on the inside... 
I hope you find this week's 'Excel In Minutes' tip helpful. If you like this weeks tip, please share it and recommend your colleagues and any other Excel users you know to sign up for my newsletter. Keep on Excelling, Francis Hayes (The Excel Addict) Email: fhayes[AT]TheExcelAddict.com |
||
|
|
||
|
|
||
| Missed my last newsletter? | ||
Click Here to View it Online |
||
|
|
||
| |
||
|
||
|
If you have a favourite quote, send it to me and I
may post it in my newsletter.
|
||
THIS WEEK'S 'EXCEL IN MINUTES' TIP |
||
| Calculating
Elapsed Time Extracted From Text
Strings |
||
This week I received an email from Lori who was having some trouble working with data she downloaded from their new computer system. Lori is with the sheriff's department and one of the reports she prepares calculates elapsed time between 'dispatch time' and 'on-scene time'. Problem is, the data she is getting from their new system has the times formatted in 6-character text strings and are separate from the dates.  In this example, you can see that the first dispatch time (cell B2) is 5:41:13 PM and the on-scene time (cell D2) is 5:47:57 PM. Here is the solution I suggested for Lori... Step 1: Extract the 'dispatch time' from the text string To convert a 6-digit text string to a time value you have to extract each part (hours, minutes, seconds) and put them into a TIME function. TIME(hour,
minute, second)
You could use LEFT(B2,2) to extract to left 2 characters, MID(B2,3,2) to extract the middle 2 characters and RIGHT(B2,2) to extract the right 2 characters. But, personally, I prefer to use the MID function to extract all of the parts from a text string. I think it's simpler to understand because you are using the same function for all parts.  So for the first CALL DISPATCH TIME use the formula…
=TIME(MID(B2,1,2),MID(B2,3,2),MID(B2,5,2))
(see
alternate time_formula below)
MID(B2,1,2): From
cell B2, start at the 1st character and extract 2
characters.
MID(B2,3,2): From cell B2, start at the 3rd character and extract 2 characters. MID(B2,5,2): From cell B2, start at the 5th character and extract 2 characters. This will give you TIME(17,41,13) or 5:41:13 PM in cell E2. Step 2: Format the result as Time Now, you will need to format this cell as Time. From the Home tab, click the Number format dropdown, click More number formats..., choose Custom at the bottom of the Category list and type hh:mm: ss in the Type field.  Step 3: Combine the date and time for elapsed time that spans multiple days When you are calculating elapsed times that could span multiple days, you'll need to use both the date and time in your calculation. Simply add the date from cell A2 to the time formula in E2 to get both the date and time into one cell.
=A2+TIME(MID(B2,1,2),MID(B2,3,2),MID(B2,5,2))
(see
alternate date_time_formula below)
Now you'll need to format that cell with a date & time format. I used a custom number format dd-mmm-yyyy hh:mm:ss. If you prefer, you can use an AM/PM time format (e.g. mmm-yyyy h:mm:ss AM/PM)  Step 4: Copy this formula to extract the 'on-scene' date and time Now you can copy the formula (cell E2) to get the result for the 'on-scene' date and time. You'll need to copy it two columns to the right (cell G2) so the cell references in the formula will adjust to refer to the date and time cells two columns to the right (C and D). Formula in cell E2: =A2+TIME(MID(B2,1,2),MID(B2,3,2),MID(B2,5,2))
Same formula when copied to cell G2: =C2+TIME(MID(D2,1,2),MID(D2,3,2),MID(D2,5,2))  Step 5: Calculate elapsed time Now, in column H, you can calculate the elapsed time simply by subtracting the DISPATCH date and time (E2) from the ONSCENE date and time (G2). Give that cell a TIME format hh:mm:ss and now you’ve calculated the elapsed time from the DISPATCH to the ONSCENE in hours, minutes, and seconds.  Elapsed times greater than 24 hours If your elapsed time could result in greater than 24 hours, you will need to modify the time format by wrapping the hours code in square brackets. [hh]:mm:ss
|
||
|
|
||
To share this tip with your friends and
|
||
|
|
||
 |
||
|
|
||
 |
||
| Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose. | ||
|
"Spreadsheets Tips From An Excel
Addict" is a weekly publication of
TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved. 8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630 |
