Calculating Time with Excel Formulas

How often have you looked at your watch and typed the current time into your worksheet? Or tried to convert from one time zone to another and produced an error? Or wondered why a time shows up looking like “0.9285”?

Fortunately, these questions are easy to answer once you understand how Excel time formulas work. This article:

    • Explains some basic time formulas such as NOW() and TIME(),
    • Shows how to add and subtract times, and
    • Uses these in two sample workbooks: a timesheet calculator and a time zone converter.

The NOW() Function

NOW() returns the current date and time from your computer.

Fred Pryor Seminars_Excel Time Formulas Figure 1

You can reformat the result by right-clicking the cell, choosing “Format Cells,” and picking a different time format.

Fred Pryor Seminars_Excel Time Formulas Figure 2

Fred Pryor Seminars_Excel Time Formulas Figure 3

If you want only the time and not the date, you can either format it as a time and pretend the date doesn’t exist, or you can use “=NOW()-TODAY()”, which subtracts the date portion and leaves only the time portion. Regardless of how you format the display, however, the underlying value remains constant until you recalculate.

Whenever you recalculate (or whenever Excel recalculates automatically because other cells have changed), the function pulls the time as of that moment. Use this function when you need an always-updated current time in your calculations.

NOTE: Someone who opens the worksheet in six months will see her own current date and time in this field, not the date and time that you saved the workbook.

The Shortcut Control-Shift-;

When you need to save a permanent record of the current time, hold down the Control key and the Shift key while you type a semicolon (;). Like NOW(), it returns the current system time. Unlike NOW(), however, it stamps the time as a fixed value. You can change the format, but the value is always the same.

Similarly, you can save the current date by holding down the Control key (but not the Shift key) and typing a semicolon.

Because it saves the value and not an updateable function, someone who opens the worksheet in six months will see the date and time that you stamped on the page, not her own current date and time.

The TIME() Function

Both the NOW() function and the Ctrl-Shift-; shortcut return the current system time. What if you need to store a value for some other time?

When you need to convert a specific number of hours, minutes, and seconds to an Excel time, use the TIME() function with the parameters the numbers of hours, minutes, and seconds. Typing “=TIME(14, 30, 10)” produces:

Fred Pryor Seminars_Excel Time Formulas Figure 4

Be sure to enter the hours using a 24-hour clock.

Adding and Subtracting Times

When you need to add or subtract a fixed number of hours, minutes, or seconds to a time value, just add or subtract the way you would any decimals. If you add 4 hours to 11:30 AM, you get 3:30 PM:

Fred Pryor Seminars_Excel Time Formulas Figure 5

To understand time arithmetic more fully, you need to understand first how Excel stores times.

Try this: in an empty cell, type “=NOW()” and hit <ENTER>. The result shows the current date and time, probably formatted like this:

6/26/2014 9:57

 Next, right-click the cell, choose “Format Cells,” and pick “General.” The result should look something like this:

41816.41498

 This is how Excel stores date/times—the date is the number to the left of the decimal, and time is the decimal portion of the number, where 0 corresponds to 12:00 AM, 1 is midnight, and every other time of the day falls somewhere in between. Times are decimals values between 0 and 1.

To convert a time to its decimal value, divide the hours (using the 24-hour clock) by 24 (the number of hours in a full day):

  • 12:00:00 PM (Noon) is exactly halfway through the day, so the decimal value is 1 / 2 = 0.5.
  • 6:00:00 AM is one-fourth of the day, so the decimal value is 1 / 4 = 0.25.
  • 9:00:00 PM corresponds to 21:00:00, so the decimal value is 21 / 24 = 0.875.

On the other hand, to convert a time decimal value to the number of hours, multiply by 24.

Because times are simply numbers between 0 and 1, you can do any arithmetic on times, just as you would with any other decimal numbers. Add them, subtract them, multiply them by two, divide them by four—just like any other decimal numbers.

But there are a couple of warnings as you get into date arithmetic.

Why Does 15 hours + 18 hours = 9 hours?

Try this, using a 24-hour format:

Fred Pryor Seminars_Excel Time Formulas Figure 6

 

Right-click on Cells A1 through A3, choose “Format Cells,” and select “General.”

Fred Pryor Seminars_Excel Time Formulas Figure 7

This shows that Excel did, in fact, add the two time values, which resulted in a number greater than one, meaning more than a full day. Like an odometer, however, Excel’s standard time formats roll over and show only the decimal portion—0.375, which equals 9:00:00.

When you need to show hours greater than 24, such as when adding up hours from a timesheet, choose a time format that displays the larger numbers:

Fred Pryor Seminars_Excel Time Formulas Figure 8

When working with customer formats, surround the hours with square brackets ([]):

Fred Pryor Seminars_Excel Time Formulas Figure 9

Applying that format to this example produces 33 hours, as expected:

Fred Pryor Seminars_Excel Time Formulas Figure 10

Why Does 4 hours – 12 hours Produce an Error?

This situation arises when you’re calculating how far in advance you need to begin a task so that it will be completed by the target time. It arises also in certain time zone conversions that cross over into the prior day—such as calculating London time when it’s 5:00 AM in Tokyo. (See the time zone converter below and the error-trapping used in the calculation.)

Unfortunately, Excel does not like negative times, so you’ll need to introduce an error-trapping formula. Whenever subtracting times could result in a negative time, wrap it in an IF() statement which adds 1 to the result. (In cases which might result in something prior to one day back, you’ll need to adjust your formula even further.)

Finally, you’re prepared for useful applications of these formulas.

Timesheet Calculator

The attached timesheet calculator uses simple time subtraction to determine the number of hours worked in each segment: time out – time in. The format for hours worked is “[h]:mm,” which ignores seconds and does not display AM or PM. The calculation traps for negative numbers using an IF() statement: if the time in is greater than the time out, then it displays the message, “Time out must be later than time in.”

Next, the calculator adds the duration of each segment in each day to reach a daily total. Again, this is simple time arithmetic: “=E8 + E9”.

The weekly total sums the daily totals and uses a format of “[h]:mm.” The square brackets around the hours allow this formula to show values greater than 24 hours.

Finally, the overtime formula determines whether the number of weekly hours is greater than 40. Remember that 40 must first be converted into a fraction of a day by dividing by 24, so the formula compares the hours worked to 40/24. If greater, then the overtime formula subtracts 40/24 from the hours worked and returns the difference. If less, then the overtime formula returns 0:00.

Fred Pryor Seminars_Excel Time Formulas Figure 11

Time Zone Converter

In the attached time zone converter enter a starting time and time zone in Columns A and B (in the gold cells).

Fred Pryor Seminars_Excel Time Formulas Figure 12

Column C converts the local time to Greenwich Mean Time, also known as UTC (Coordinated Universal Time), by subtracting the differential from GMT. Remember that the differential (in hours) must be converted to a decimal first by dividing by 24. Because this could result in a negative time if the local time is far east and is early in the morning, the formula uses an IF() statement, adding a 1 if the result would be negative.

In Columns D, F, and H, enter target time zones for the conversion. Columns E, G, and I calculate the target times by starting with the GMT base and adding the differential from GMT.

All of these calculations use data from the table on the “Time Zone Data” tab. Feel free to add records, for this table is far from including all worldwide time zones. Note that you can define the time zone abbreviation in Column A using any format that you like. The sample data included shows some with abbreviations (GMT, EST, PDT, etc.) and others using cities. Whatever values you enter here will show up in the pick list for entering data in Columns B, D, F, and H of the converter.

Next Steps

This is just the beginning of calculating formulas using times. From this point, you can develop far more complicated formulas, such as determining the average time required to complete a task, the maximum time between phone calls, etc. As long as you remember that times are just decimal numbers that represent portions of a day, you can work through any remaining calculations. You have the tools, and you know how to use them. Enjoy!

Local Seminars Related to this Topic:

Related Advanced Excel Articles

Categories

Monthly Archives

20 Comments

  • Walter Chin says:

    I would like to see if there is a function for excel to continuously run a clock. One column has a set clock one has interval number of 10-20-30- to several hours.

    final

  • Annette Morgan says:

    How can I create an If function to subtract 15 minutes from the time if the value is => 15 minutes. Here are the formulas on my spread sheet. C & D = times (6:00 am and so forth) the formula in cell E is as follows =SUM(D4-C4) 6:00 am – 6:15 am = 0:15. The formula in cell F is =SUM(E4*24) which brings back a decimal number that would be .25 for the time I have here. I need F to subtract :15 or .25 if E => 0:15 and do nothing if the value of E is 0:00 to 0:14.

  • Gabriella says:

    I need to create a conversion from 3 to 8 minutes to .1, 9 to 14 minutes to .2, 15 to 20 minutes to .3, 21 to 26 minutes to .4, 27 to 32 minutes to .5, 33 to 38 minutes to .6, 39 to 44 minutes to .7, 45 to 50 minutes to .8, 51 minutes to 56 minutes to .9, and 57 minutes to 60 minutes to 1.0

  • Adam White says:

    The issue I am having is that i have 2nd shift workers who work from 3 pm to the following morning 2 am and this formula will not work +IF((((D11-C11)+(F11-E11))*24=8,8,((D11-C11)+(F11-E11))*24)
    now what would i have to do to allow excel to recognize a formula to allow it to come to lets say 10.5hours from start3pm stop9pm start9:30pm stop 2am it is allways fine up until right after the 11:59 hour where the new 24 hour day starts.

  • Paul says:

    How do I calculate overtime when rate only kicks in after 5 hours of Ot

    • Excel Tips and Tricks from Pryor.com says:

      Hello Paul,

      In order to calculate overtime for a 45 hour work week, you will use the formula

      =total amount of time worked – (45/24)

      Using the blog example, the red arrow in the image below shows the results of this formula:
      overtime reply

  • Karen says:

    Hi, i can’t seem to see the if statement you have used in the time zone convertor. I need to subract 5 hours but when i try that on 00:37:02 i am getting -04:22:58. How do I sort this?

    Thanks

    • Excel Tips and Tricks from Pryor.com says:

      Excel can be a little overly-exact when working with time math. For instance, in the example -> 00:37:02 minus exactly five hours is -04:22:58. Just like 50:00:00 minus exactly twenty five hours is 25:00:00. The confusion we have arises from the idea that 00:37:02 is “Thirty seven minutes and two seconds after midnight.” As stated, it’s not. It reads “zero hours, thirty seven minutes and two seconds”. Excel is ready to do math with hours, minutes and seconds in the same fashion as dollars and cents are handled. This allows you to add up hours of, say, 1,000 people who all worked 10 hours. – that would be 10000:00:00. 10000:00:00 means “ten thousand hours” and that’s the correct answer you’d get for 10:00:00 times 1,000. Excel wont treat 10,000 hours as 416.66 days unless you tell it to. To imply “Thirty seven minutes and two seconds past midnight of a day” you must include a date. What you want is: “Five hours closer to Tuesday starting at 37 minutes and 2 seconds into Wednesday” then you need to tell Excel that its dealing in dates and time.
      For example, here’s that same problem, expressed with a date:
      Subtract Time
      Now that Excel knows I want to subtract from one day into the previous day, in 24 hour cycles (and I’m not just doing math with hours, minutes and seconds), it can subtract five hours from the first day (A1) and know that it’s crossing midnight into the previous day. The formula for C1 is just A1-B1.
      Be sure to format your cells in A1 and C1 as CUSTOM (as below with dates and times) or it becomes possible to get the right answer and not be able to tell that it is.
      Custom

  • Ashikur Rahman says:

    Dear,

    How can I calculate number of hours from today time to tomorrows time in excel.

    Ashik

    • Excel Tips and Tricks from Pryor.com says:

      Excel is very adept at treating time as a type of number that you can do math with.
      Excel treats days as a decimal value. A whole day is 1.00. A day and a half is 1.50.
      In the example below, cell A1 contains midnight on a given Tuesday in June. (the first second of that Tuesday)
      B1 contains the exact time of the next day at noon.
      1 day (24 hours) plus noon of the next day (12 more hours) is 36 hours exactly.
      Reply
      So the calculation to get the difference in hours is just days*hours.
      Cell C1 is =(B1-A1)*24
      *Note: Be sure that the cells are formatted for date AND time. If we make Excel guess at the time it will default to midnight.

  • Gina says:

    Im trying to caculate how long it took to complete a task using a 24hr clock. Ive changed the format to h:mm but it continues to subtract the numbers as if counting to 100 instead of 60 what am i doing wrong

    • Excel Tips and Tricks from Pryor.com says:

      We weren’t able to replicate the “counting to 100” problem so we took the idea from scratch.
      In the example below, A1 and B1 contain date/times.
      C1 contains “=B1-A1” and the answer is 3.81 days (the default format is days)
      D1 contains “=C1*24” which gives the same answer in hours. (24 hour days)
      If you wanted that answer in minutes you could say “=C1*24*60”

      Reply

  • Waseem J says:

    How can i make a sheet where i can be able to count overtime such as, 30 minutes or above to be count as 1 hour, 1:30 or above to be as 2 hours, 2:30 or above to be count as 3 hours etc.
    Please help me in this matter.

    • Excel Tips and Tricks from Pryor.com says:

      What you’re describing is called time-rounding.

      In decimal calculation, if you choose to round, 2.5 becomes 3.0 and 4.2 becomes 4.0 (rounding up starts at .5 and everything else rounds down)

      Excel has dozens of functions equipped for decimal rounding. Excel is also well equipped to round time notation. Specifically, a function called MROUND() will be of great assistance. It’s an advanced formula but relatively easy to apply.

  • Alline says:

    I have this problem, it a small part of a big one I have:

    Team Machine
    1 2 3 4 5 Total (hs) Total ($)
    A 00:50 00:00 16:25 06:32 04:22 28:09 ?
    B 00:35 00:00 05:31 05:06 01:40 12:52 ?
    C 86:06 00:00 22:19 17:22 10:15 136:02 ?

    I want to calculate the cost per team based in the time use of each machine.

    Each machine has a different cost per hour, so the formula in the Total($) should be i.e. x:y =((HOUR(x)+MINUTE(y)/60) for each time (team x machine) and sum – for me it’s ok, I didn’t have problems for the results except with big times.

    The biggest problem is values as 86:06 – it’s showed as it is but Excel in the formula bar shows me 03/01/1900 14:06:00 for this value – so obviously the calculation result will be wrong as it’s not assuming the proper time.

    Can you help me with that?

    How can I make Excel assume 86:06 as really 86 hours and 06 minutes?

    • Excel Tips and Tricks from Pryor.com says:

      Would you be surprised to know you actually have the right answer (formatted oddly)?
      Here’s how that works and how to fix it:
      -Excel thinks of time in terms of fractions of a date.
      -Even when you don’t see a date (due to formatting), one is present.
      -If you type in a time, and give it no date reference, it assumes that the date to apply is 1/1/1900
      -If you mark 86 hours and 6 minutes past the start of 1/1/1900, you get 1/3/1900 14:06 (your system formatted that as day first 3/1/1900)

      If you format your answer as Time (see below) you’ll instantly see that you’ve already got the right answer: 136 hours and 2 minutes.
      reply

  • Jo says:

    What is the formula to subtract 2 times using the 7/8 rule? ie.. up to 7 minutes rounds down and 8+ minutes rounds up? 10:55am to 12:20pm = 1.25 hours.

    • Excel Tips and Tricks from Pryor.com says:

      The most direct formula to round time, using the 7/8ths rule is:
      =ROUND(A1/(1/96),0)*(1/96)
      (Where A1 is the cell needing rounding)
      In the example below, column A is the time needing to be rounded, and column B is the rounded result.
      reply
      Be sure that all the times, involved, are formatted for “Big time” or time that exceeds 24 hours (see below)
      reply2

  • Sheila says:

    I need to create a calculator that will reduce any given date and time by 11 hours. How would I do that in excel?

    • Excel Tips and Tricks from Pryor.com says:

      The important part of doing time math in Excel is formatting your cells to show the needed answers.
      It is entirely possible to do the exact right thing and get an answer that looks wrong but is, in fact, correct.

      In this example, column A is formatted for HH:MM:ss only
      Column B shows a date/time and is formatted for it.
      Column C is formatted identically as column B.

      The values in yellow show each value in column B, minus the value in Cell A1 (11 hours exactly)
      Each C value is the B value, eleven hours in the past.
      reply
      Please note that the last row crosses midnight, resulting in an answer that is on the previous day.
      Also note, that we’ve subtracted an absolute reference (with $$ signs) so there is no need to repeat “11:00:00” on each row.

Leave a Reply

Your email address will not be published. Required fields are marked *