TIME conversions in Microsoft Excel.

          We’ve covered a lot of ground with posts on date calculations in Excel: using the DATEDIF function, calculations without the DATEDIF function , and in the most recent post – business day calculations with the NETWORKDAYS funtion. Now it’s turn to perform some TIME conversions and calculations in Excel. We will use TIME, TIMEVALUE, NOW, HOUR, MINUTE, SECOND, TEXT, and MOD functions to perform various time operations…

          NOW function displays the current date and time. Depending on your cell formatting, it might be date only, or if your cell format is General, it could even be serial number equivalent of your current date and time. While this function has no arguments, its syntax still calls for a set of parenthesis: =NOW(). We already know that Excel uses the whole value of 1, in reference to the “beginning of time”, as far as Microsoft is concerned – January 1, 1900. Similarly, today’s date has the value of 42,124 The decimal point value references the fractional time portion of any date. As an example, 0.5 denotes NOON, while 0.75 refers to 6 PM. [0.75*24 = 18] Similarly, one minute, is 1/60th of an hour or 1/1440th of a day, calculating to be 0.069(4). Keep in mind that, whenever date/time value starts with a 0, the date portion has no value, and we are working with the time value only.

          We can use TIMEVALUE function in Excel to output the decimal number of the input time string. =TIMEVALUE(time_text). Using an example of 4.01PM:

=TIMEVALUE("4:01 PM")

would result in the value of 0.667361111 :

          Excel’s HOUR, MINUTE, and SECOND functions are fairly self-explanatory; in that they return HOUR, MINUTE, and SECOND values from the time string. Following their simple syntax, we can perform required manipulations:

=HOUR (serial_number)

=MINUTE (serial_number)

=SECOND (serial_number)

Using our 4:01:03 PM example, we have:

          Should we ever need to find numeric value for the time stamp of our choice, we could use the TIME function, which returns the decimal number for a particular time.

=TIME(hour, minute, second)

Or using the above time, we would have:

=TIME(16,1,3)

If you don’t see the value of 0.6673958(3), you might want to format your cell to have a Number format.

          According to Microsoft , Excel’s TEXT function “converts a numeric value to text and lets you specify the display formatting by using special format strings. This function is useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols.”

=TEXT(value, format_text)

         Using their examples, we could come up with the right time format for our needs:

          As evident from the illustration starting this post, algebraic time calculations are only possible in Excel, when we don’t overlap days. (Both beginning and end times are before midnight.) Since, this is not always the case, we could use the help of MOD function to achieve correct result. This function returns the remainder after a number is divided by a divisor:

=MOD(Number, Divisor)

          Feel free to share your own Excel TIME tricks in the comments section!