Excel Remove Minutes from Time: 3 Easy Ways
To remove minutes from a time value in Excel, you round the time down to the nearest hour using the FLOOR function. For example, =FLOOR(A1,"1:00") turns 9:45 AM into 9:00 AM. This article explains the formulas, how they work, and when each is best.
Why You Might Need to Remove Minutes
Excel stores times as fractions of a day, so 12:00 PM is 0.5, and 6:30 AM is about 0.2708. Sometimes you need to strip the minutes for reporting, scheduling, or billing. Common cases include:
- Converting clock-in/out times to whole hours for payroll.
- Grouping timestamps into hourly buckets for charts.
- Rounding appointment times to the nearest hour for a calendar view.
Unlike simply changing the number format (which only hides the minutes), the methods below actually change the underlying value so calculations use the rounded time.
Method 1: FLOOR to Round Down
The FLOOR function rounds a number down to the nearest multiple of a given significance. For times, the significance is the hour unit, written as "1:00" or TIME(1,0,0).
Formula: =FLOOR(A1, "1:00")
If A1 contains 10:45 AM, the result is 10:00 AM. If it contains exactly 10:00 AM, the result stays 10:00 AM. This always goes backward in time, never forward.
Method 2: INT or HOUR to Extract the Hour
Another approach is to extract just the hour and rebuild a time with zero minutes:
Formula: =TIME(HOUR(A1), 0, 0)
This takes the hour from A1, sets minutes and seconds to zero, and returns a new time. For 3:48 PM, it returns 3:00 PM. It behaves the same as FLOOR for positive times, but it can be easier to read for beginners.
You can also use =INT(A1*24)/24 which multiplies the time by 24 (to get hours), drops the decimal, and divides back. This is a numeric trick that works because Excel times are fractions of a day.
Method 3: MROUND to Round to Nearest Hour
If you want to round to the nearest hour (not just down), use MROUND:
Formula: =MROUND(A1, "1:00")
This rounds 10:45 AM up to 11:00 AM, 10:15 AM down to 10:00 AM, and 10:30 AM to 11:00 AM (since 0.5 rounds up). Use this when you need symmetric rounding rather than always removing minutes.
How to Choose the Right Method
- Always remove minutes (floor): Use
FLOORorTIME(HOUR(...),0,0). Both give the same result for positive times. - Round to nearest hour: Use
MROUND. - Need the hour as a number (e.g., 9, not 9:00): Use
=HOUR(A1)directly. - Working with negative times (rare):
FLOORwith a positive significance rounds away from zero, which may not be what you expect. Test with your data.
Common Mistakes and How to Avoid Them
One frequent error is using ROUND(A1,0) which rounds to the nearest whole day, not hour. Another is formatting the cell as a number instead of time—if you see 0.375 instead of 9:00 AM, change the number format to h:mm.
Also, remember that FLOOR requires the significance argument. If you omit it, Excel returns #NUM!. And when copying formulas, check that the time cell references are correct—absolute vs relative references matter if you fill down.
Formatting the Result
After applying any of these formulas, the result is still a time value. To display it as a time, select the cells and press Ctrl+Shift+1 (or go to Format Cells > Time). If you want to see only the hour without “:00”, use a custom format h or h AM/PM.
If you need the result as a decimal number of hours (e.g., 9.0 for 9:00), multiply the time by 24 and format as a number.
Frequently Asked Questions
How do I remove minutes and seconds from a time in Excel?
Use =FLOOR(A1, "1:00") to round down to the hour, which removes both minutes and seconds. Alternatively, =TIME(HOUR(A1),0,0) also sets seconds to zero.
What is the difference between FLOOR and INT in Excel?
FLOOR rounds down to a specified multiple (like an hour), while INT rounds down to the nearest integer. For times, INT(A1*24)/24 is a way to use INT to get the hour, but it’s less direct than FLOOR.
Can I remove minutes without a formula?
No, changing the number format only hides the minutes; the underlying value still contains them. You need a formula to actually change the value.
Why does my formula return 0 instead of a time?
If you see 0, the cell is probably formatted as a number. Change the format to Time. Also check that the time is stored as a time, not as text—if it’s text, use TIMEVALUE first.
How do I round to the nearest 15 minutes instead of an hour?
Replace "1:00" with "0:15" in FLOOR or MROUND. For example, =FLOOR(A1, "0:15") rounds down to the nearest quarter hour.
Does FLOOR work with times that cross midnight?
Yes, as long as the time is stored as a proper Excel time (a fraction of a day). For example, 1:00 AM is 0.0417, and FLOOR will round it correctly.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.