Calculate with Time

July 11, 2022 - by Bill Jelen

Problem: I bill my clients hourly. How can I convert Excel times to decimal hours so that I can do my billing?

Strategy: Multiply the Excel by 24 to come up with a decimal number of hours. You can them multiply the hours by the hourly rate to calculate the billing.

With Time of 65:00 hours in B2, convert to decimal hours with a formula of =B2*24 — Figure 565. Multiply times by 24 to get hours.

Problem: I need to do calculations by hour.

Strategy: There are functions HOUR, MINUTE, and SECOND to break a time into components. There is a function TIME(Hour,Minute,Second) to put time back together.

A2:A4 contain hours and minutes. To extract the hour from the time, use =HOUR(A2). To truncate the the previous hour, use =TIME(HOUR(A2),0,0). To round to the nearest hour, use =MROUND(A2,(1/24)). — Figure 566. Time calculations.

This article is an excerpt from Power Excel With MrExcel

Title photo by Agê Barros on Unsplash