Reddit
reddit.com › r/excel › shared excel spreadsheet- floor formula not correctly rounding down
r/excel on Reddit: Shared Excel spreadsheet- Floor Formula not correctly rounding down
July 30, 2025 -
Good morning,
Im needing a formula to calculates time rounded down to the nearest 15 minute mark.
Ive been using the floor function, however if the time difference between departure and appointment time is, say 5 hours. The floor function calculates it as 4.75 hours.
I ran into this issue on excel and found it to be an arithmetic issue, which I corrected by taking the hours and adding 10-8: =Floor(((F2-D2)+10-8), Time(0,15,0))*24
This doesnt work on a shared Excel spreadsheet though. So how would I calculate it?
For example I want it to look like this
3:59 should be 3:45 5:00 should be 5:00
I tried Rounding it first then flooring it however that still doesnt work in a shared Excel spreadsheet
Exceljet
exceljet.net › floor function
Excel FLOOR function | Exceljet
September 13, 2021 - For more control over how FLOOR rounds negative numbers, see the FLOOR.MATH function. MROUND rounds to nearest values based on a supplied multiple. When you supply "0:15" as the multiple, Excel internal converts 0:15 into 0.0104166666666667, which is the decimal value that represents 15 minutes, and rounds using that value. You can also express 15 minutes in a formula with this... If you need to group times into buckets (i.e.
Videos
01:31
How to Use the FLOOR.MATH Function in Excel to Round Data to a ...
How to Round Down Times in Excel - YouTube
Microsoft Excel - Using the CEILING and FLOOR functions
Round Time to the Nearest 5, 10, or 15 Minutes in Excel
Round time to nearest 15 minutes MROUND, FLOOR or ...
00:45
Excel Formula to Round Time to the nearest 15 minutes - YouTube
MrExcel
mrexcel.com › forums › question forums › excel questions
Unexpected results of FLOOR function with time values | MrExcel Message Board
August 22, 2023 - I would like to understand why the FLOOR function does not work as expected sometimes. Click to expand... Hi, the problem is almost certainly caused by "floating point rounding errors" (an internet search will reveal lots of information on the subject) - the TIME() function appears to round these errors better so you could jump through a few hoops like this to get to the right answer. Excel Formula: =FLOOR(LET(t,TIME(8,0,0)-"07:30",TIME(HOUR(t),MINUTE(t),SECOND(t))),"00:30") Upvote 0 ·
Excel Easy
excel-easy.com › examples › rounding-times.html
Rounding Times in Excel (Easy Steps)
Use MROUND in Excel to round a time to the nearest 15 minutes, the nearest 30 minutes, the nearest hour, etc. 1. The formula below rounds a time to the nearest 15 minutes. Note: if your formula returns a decimal number, don't panic.
Capitalize
capitalizeconsulting.com › home › excel date and time functions
Excel Date and Time Functions: Learn and Excel
March 22, 2018 - In our example, those dates less ... words, if we ignore the remainder, always rounding down, then the formula of (Days from the Start / 7 + 1) would give you the Week....
Ablebits
ablebits.com › ablebits blog › excel › excel formulas › rounding in excel: round, roundup, rounddown, floor, ceiling functions
Rounding in Excel: ROUND, ROUNDUP, ROUNDDOWN, FLOOR, CEILING functions
May 3, 2023 - For example, both of the formulas =MROUND(3, -2) and =MROUND(-5, 2) result in the NUM error. The FLOOR function in Excel is used to round a given number down, to the nearest multiple of a specified significance.
MrExcel
mrexcel.com › forums › question forums › excel questions
Floor Function for Time Interval | MrExcel Message Board
April 5, 2019 - Assuming your times are in A1:A11, I can replicate your results using: B1: =FLOOR(A1,2.5/1440) B2: =IF(A2-INDEX(A$1:A1,MATCH(B1,B$1:B1,))>=2.499/1440,FLOOR(A2,2.5/1440),B1) copy down Is this what you're looking for? It means that any particular time may be "rounded" differently depending on the distribution of other times in its "group"?
Excel Tip
exceltip.com › home › excel date & time formulas
Rounding Times Down to a Specified Time Increment in Microsoft Excel 2010
January 10, 2015 - In this article, we will learn how toround the times down to a specified time increment.We will use a combination of“TIME”, “HOUR”, “MINUTE” and “FLOOR” functionsin Microsoft Excel 2010. FLOOR: - This function is used to round a number down … Continue reading →
Top answer 1 of 11
23
Since you said you also want the date, how about this:
= (ROUND((A1 * 1440) / 15, 0) * 15) / 1440
Assuming that A1 has the date/time value you want. This takes advantage of the fact that date/time columns in Excel are just numbers (integer portion is the date, fractional portion is the time)
2 of 11
7
If you want to round to the Nearest 15:
Assuming your time is in cell A2
We'll put our new time into B2:
B2 =TIME(HOUR(A2), ROUND((MINUTE(A2)/60)*4, 0) * 15, 0)
If you wanted to always round up or down you replace ROUND with ROUNDUP or ROUNDDOWN
ExtendOffice
extendoffice.com › documents › excel › how to round time to nearest hour/minute/second in excel?
How to round time to nearest hour/minute/second in Excel?
Remember to use the right format for result cells. If you need to round down to the previous whole hour (regardless of the number of minutes or seconds), use: =FLOOR(A2,1/24) This is often used for business cutoff times or scheduling where all times should default to the previous full hour.
DataCamp
datacamp.com › tutorial › excel-floor
Excel FLOOR(): How to Round Down Numbers in Excel | DataCamp
July 1, 2025 - FLOOR() is also great for managing time, especially when you need to group durations into consistent intervals. Imagine you want to fit any number of minutes into 15-minute blocks: ... This returns 45, so 52 minutes gets slotted into the 45-minute block. Excel does offer several rounding functions, ...
ExcelDemy
exceldemy.com › home › excel formulas › rounding time in excel to nearest hour: 6 easy methods
Rounding Time in Excel to Nearest Hour: 6 Easy Methods - ExcelDemy
July 7, 2024 - The formula here uses the FLOOR Function to convert the time of cell C5 to the hour. In Excel, 1 hour equals 1/24 and that’s why we set the significance as 1/24..
MyExcelOnline
myexcelonline.com › home › the ultimate guide to floor function in excel
The Ultimate Guide to Floor Function in Excel | MyExcelOnline
December 22, 2024 - The syntax is =FLOOR(number, significance), where both number and significance must share the same sign. It is highly useful for financial tasks like rounding prices or aligning currency values to meet accounting standards.
Spreadsheeto
spreadsheeto.com › home › blog › how to use the floor function in excel step-by-step
How to Use the FLOOR Function in Excel Step-by-Step
January 10, 2026 - The FLOOR function in Excel rounds numbers down to the nearest specified multiple. It’s useful for financial, time, and unit conversion calculations.
Reddit
reddit.com › r/excel › how to floor a datetime to the start of the minute in power query (m language)
r/excel on Reddit: How to floor a datetime to the start of the minute in Power Query (M language)
February 19, 2021 -
Hi,
I'm using Excel 365 on Windows.
I would like to know how I can round down a datetime to the nearest minute, e.g. 07/02/2021 14:15:39 would become 07/02/2021 14:15:00.
There is a StartOfHour function, but I haven't found a corresponding "StartOfMinute" one
Top answer 1 of 4
5
Use Time.StartOfHour(timestamp) + Time.Minute(timestamp)
2 of 4
3
Power Query has a lot of date/time functions nowadays, but sometimes basic math is easier. This will replace the values in the column "time", but you could add a custom column if you want to keep the original values too. = Table.ReplaceValue(#"Changed Type", each [time], each Number.RoundDown(Number.From([time])*1440)/1440, Replacer.ReplaceValue, {"time"}) Excel stores datetime values as a number. The date portion is a whole number and the time portion is the decimal. 7/2/2021 12:00:00PM is 44379.5 and 7/2/2021 12:00:30 PM is 44379.5003472222. With there being 1440 minutes in a day, we can use that to multiply by and move the hours and minutes to the whole portion of the number. We can then round that number down effectively removing the seconds portion of the number. Dividing by 1440 moves the time portion back to a decimal.
Excel Forum
excelforum.com › excel-formulas-and-functions › 1365178-floor-function-not-working-when-time-to-round-down-is-equal-to-1-hour.html
FLOOR function not working when time to round down is equal to 1 hour
December 1, 2021 - The main issue I see is floating ... C2 is 0.0416666666666666 (or how ever many sixes there are). If you enter TIMEVALUE("1:00:00") into a cell and show it to its full precision, you see 0.0416666666666667....
The Bricks
thebricks.com › resources › how-to-use-the-floor-function-in-excel-a-step-by-step-guide
How to Use the FLOOR Function in Excel: A Step-by-Step Guide
To use a 15-minute increment as your significance, you must enter it in a format Excel understands: "0:15". Let’s say the time 10:23 AM is in cell A2. In a new cell, type the formula: =FLOOR(A2, "0:15")
The Bricks
thebricks.com › resources › how-to-round-time-in-excel
How to Round Time in Excel
Round the end time down: In D2, use =FLOOR.MATH(B2, "0:15"). So 4:58 PM becomes 4:45 PM. Calculate the difference: In E2, use the formula =D2-C2. This gives you the duration in Excel's decimal format (e.g., 0.35417).
Atlaspm
atlaspm.com › toms-tutorials-for-excel › toms-tutorials-for-excel-rounding-times-by-the-minute
Tom’s Tutorials For Excel: Rounding Times by the Minute – Tom Urtis
The last formula rounds down the marathon times in column B to their lower previous minute. That formula in cell E3 and copied down is =FLOOR($B3,"00:01:00")
SpreadsheetWeb
spreadsheetweb.com › home › how to round time values in excel
How to round time values in Excel
December 17, 2021 - For general rounding through both sides, choose the MROUND. Otherwise choose between CEILING.MATH or FLOOR.MATH to round up or down respectively. The trick is to use the TIME function to return a valid time value as a multiplier/significance.