We talk about calculations with dates in Advanced Formulas training (hooray for DateDif!). Working with time in Excel can be a bit trickier. I have seen people give up when formulas don’t work initially, but sometimes all that is needed is a change in formatting. Let’s see how we can work with time in a common example.
Exercise File
To follow along, download the exercise file here: TimeCalcExercise
- In this file is a list of times that you attended some fictional February meetings. You want to figure out how much time you spent in meetings last month.
- Note that there are two sheets in this workbook: February Meetings, which is your challenge, and Solution, so you can check your work as we go.
Number Formats
You are going to see that the formulas themselves are going to be very simple: basic addition and subtraction. The real key with times is the Number Format.
We are going to be using several Custom number formats. In the custom time formats, h stands for hours, and m for minutes.
- h:mm AM/PM: 12 hour time, indicated by AM/PM, for example 11:00 PM
- h:mm: 24 hour time, not including AM/PM, for example 11:00.
- [h]:mm: Elapsed time, beyond a 24 hour clock. With this you are adding total number of hours, not adding hours to a clock of revolving time.
Start and End Time
The first thing we should do is fix up the start and end time formatting so Excel will know what we are trying to calculate. I created a couple helper columns in Columns C and D called Extract Start and Extract End. Columns A and B are a common format that happens when you export time from other programs.
Extract Start and End Time with Flash Fill
There are a number of ways you might chose to do this, but as a fan of Flash Fill, that is the route I would like to take.
- Create a pattern: click into C2 and type 9:00 AM (from A2)
- Select C3 an type 11:00 AM (from A3)
- Select C4. Go to the Home tab, Editing Group, Fill Dropdown an select Flash Fill
This should complete the rest of Column C. If it doesn’t, double check that you have established a pattern that matches what you are seeing in Column A.
Complete Column D the same way, extracting the end time from Column B.
Check Number Format
Let’s check the number format for columns C and D.
- Select C2 through D32.
- In the Home tab, Number group, click the More arrow in the lower right.
- The Type should display as h:mm AM/PM. If it doesn’t you can type it in manually, as it is listed below. This means you are view time in a way most of us are familiar with: for example, 12:30 PM.
We are going to revisit this number format area again soon, so stay tuned.
Calculating Time Spent in Each Meeting
Let’s calculate the time spent in each meeting, and then we can move on to calculate a grand total.
Subtracting Time
Column E, Total Time, is meant to calculate the time spent in each meeting. This is going to be a simple subtraction.
- Select Cell E2.
- Type =D2-C2 . You can also select cells D2 and C2 as you are creating the formularather than typing the names of the cells.
- Press enter to calculate.
4. Auto Fill the rest of the column, either by clicking and dragging or double clicking on the auto fill handle.
Adjust Number Formats
Let’s check the number format of Column E. Excel may have assumed that you wanted to use the same number formatting as C and D, but remember that includes AM and PM, which isn’t relevant for Column E.
- Select Cells E2 through E32.
- In the Home Tab, Numbers group, press the More button at the lower right.
- This time, you want the number format to be simply h:mm. So no AM/PM necessary. Make sure you are on the Custom category.
Click into the Type field and type h:mm (or find it in the type list).
Calculating Grand Total
- In Cell A33, type Grand Total.
- Select Cell E33: This is where we are going to calculate the total time.
- In the Home tab, Editing group, select the AutoSum dropdown and select Sum to add all the times in column E.
Grand Total Formatting
Something is not quite right about our Grand Total.
This is all about number formatting. This Sum is displaying in the same format as the rest of column E: h:mm. This means Excel is adding the time on a 24 hour clock, which is not what we intended.
Let’s make one more number formatting adjustment.
- Go back to your Number Format options, just like above.
- In the Type, type square brackets [ ] around the “h.” This will send the message to Excel that you want a total elapsed time… beyond the 24 hour clock. So it will look like [h]:mm
![Type listed as [h]:mm](https://blogs.wichita.edu/itstraining/wp-content/uploads/2019/03/ExcelTime10.png)
Now the total elapsed time is showing in hours and minutes.
Perfect! So it looks like you have spent almost 36 hours in these fictional meetings last month.
Solution Sheet
Did you get lost somewhere along the way? Double check your answer with the Solution sheet in the exercise file.
Thoughts?
Do you have a use for calculating time in your day to day spreadsheets? Do tell!
Congratulations, Power Users!
Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser
Catherine Lawless
Jackie Boyles
Juanita Fonseca-Rodriguez
Krista Searle
