Working with Time in Microsoft Excel | Time Functions and Conversion

Posted on:  02/05/2021
Working with Time in Microsoft Excel | Time Functions and Conversion

Do you need to add or subtract time in Excel? Some time calculations are easy, but depending on how the time is entered, you may need to use the TIME function or formula to add or subtract time. For example, if you have 9am in B2, and in cell C2, you have 3, for three hours,  you can't add them with B2+C2 and get the correct answer without the TIME function.

YouTube Video

TIME Function in Excel

TIME(hour, minute, second)
The TIME function syntax has the following arguments:

Hour

Required. A number from 0 (zero) to 32767 representing the hour. Any value greater than 23 will be divided by 24, and the remainder will be treated as the hour value. For example, TIME(27,0,0) = TIME(3,0,0) = .125 or 3:00 AM.

Minute

Required. A number from 0 to 32767 representing the minute. Any value greater than 59 will be converted to hours and minutes. For example, TIME(0,750,0) = TIME(12,30,0) = .520833 or 12:30 PM.

Second

Required. A number from 0 to 32767 representing the second. Any value greater than 59 will be converted to hours, minutes, and seconds. For example, TIME(0,0,2000) = TIME(0,33,22) = .023148 or 12:33:20 AM
=B2+TIME(3,0,0)
If B2 contains 9am, the function will return at 12pm. It adds, 3 hours to 9am. 

Video Chapters

  • 00:00 Intro to chapters
  • 01:00 TIME function
  • 02:56 Add two-time numbers
  • 4:10 Add time format and number together
  • 5:25 Subtract time
  • 6:20 Total time over 24 hours
  • 7:30 Subtract time over midnight
  • 8:20 Time as a decimal
Related articles

  • Today Function in Excel

    The TODAY function is a date/time function that shows the current date based on your computer time. The TODAY function will update automatically.

  • Calculate difference in time in Microsoft Excel

    You can calculate the difference between two times in Excel. Subtract one time from the other time and format the answer for h:mm. Hour function also shown.

  • Enter military time in Excel

    If you want to type in military time in Excel, and the time show correctly, here are the steps. This method allows you to calculate the difference between two times.

Excel file used in this video

https://chrismenardtraining.com/Files/DownloadFile.aspx?FUID=4969b72a-0b2c-4f80-ac8d-2d2348bd4de0

Excel Intermediate

Chris Menard - Excel Time Video on 20210204 (Size: 13KB)
PivotTable _ Report Filters - YouTube chris menard video (Size: 32.8KB)
Chris Menard

Chris Menard is a certified Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. He runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events every year, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the Support Staff Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.

Categories