Excel 2019 Bible

Chapter 12: Using Formulas with Dates and Times

12

The formula in cell F4 is as follows:

=ROUNDDOWN(C4*24/0.5,0)*(0.5/24)

You can round a time value to the nearest hour by multiplying the time by 24, passing that
value to the ROUNDUP function, and then dividing the result by 24. For instance, this for-
mula would return 7:00:00 AM:

=ROUNDUP("6:15:27"*24,0)/24

To round up to 15-minute increments, you simply divide 24 by 0.25 (a quarter). This formula
would return 6:30:00 AM:

=ROUNDUP("6:15:27"*24/0.25,0)*(0.25/24)

To round down to 30-minute increments, divide 24 by 0.5 (a half). This formula would
return 6:00:00 AM:

=ROUNDDOWN("6:15:27"*24/0.5,0)*(0.5/24)

For more details on the ROUNDDOWN and ROUNDUP functions, see Chapter 10, “Using Formulas for

Common Mathematical Operations.”

Converting decimal hours, minutes, or seconds to a time

It’s not uncommon to get a feed from an external source where the times are recorded in decimal
hours. For example, for 1 hour and 30 minutes, you see 1.5 instead of the standard 1:30. You can
easily correct this by dividing the decimal hour by 24 and then formatting the result as a time.

Figure 12.19 shows some example decimal hours and the converted times.

FIGURE 12.19

Converting decimal hours to hours and minutes

Dividing the decimal hour by 24 will result in a decimal that Excel recognizes as a time value.

To convert decimal minutes into time, divide the number by 1440. This formula will return
1:04 (one hour and four minutes):

=64.51/1440