This should complete the rest of Column C. Go to the Home tab, Editing Group, Fill Dropdown an select Flash Fill Create a pattern: click into C2 and type 9:00 AM (from A2).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. Columns A and B are a common format that happens when you export time from other programs.Įxtract Start and End Time with Flash Fill I created a couple helper columns in Columns C and D called Extract Start and Extract End. 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.

With this you are adding total number of hours, not adding hours to a clock of revolving time.

:mm: Elapsed time, beyond a 24 hour clock.

h:mm: 24 hour time, not including AM/PM, for example 11:00.

h:mm AM/PM: 12 hour time, indicated by AM/PM, for example 11:00 PM.

In the custom time formats, h stands for hours, and m for minutes. We are going to be using several Custom number formats. The real key with times is the Number Format. You are going to see that the formulas themselves are going to be very simple: basic addition and subtraction.

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.

You want to figure out how much time you spent in meetings last month.

In this file is a list of times that you attended some fictional February meetings.

To follow along, download the exercise file here: TimeCalcExercise

Let’s see how we can work with time in a common example. I have seen people give up when formulas don’t work initially, but sometimes all that is needed is a change in formatting. Working with time in Excel can be a bit trickier. We talk about calculations with dates in Advanced Formulas training (hooray for DateDif!).

Time calculation formula for excel