Menu
How can I round times in MySQL to the nearest 15 minutes (e.g. 0,15,30,45)?
780
When reporting time not worked, employees should round to the nearest 1/4 hour. For example: 15 minutes = 0.25 hours, 30 minutes = 0.50 hours and 45 minutes = 0.75 hours.
You also can use this formula =MROUND(A2,15/60/24) to round time to nearest minute. 3. If you want to round time to previous 15 minute, use this formula =FLOOR(A2,15/60/24).
1. Enter this formula: =MROUND(A2,"0:15") or =MROUND(A2,"0:30") into a blank cell beside your data which need to round to nearest quarter or half hour, and then drag the fill handle down to the cells you want to apply this formula, and you will get a list of decimal numbers, see screenshot: 2.
SELECT SEC_TO_TIME(FLOOR((TIME_TO_SEC(CURTIME())+450)/900)*900) In this example I have used CURTIME() for the input time, but you can use any time field.
900 seconds=15 minutes (the period to round to), 450 seconds is half that (to provide the rounding element). I've tested with 1800/900 to get nearest half hour, should work with others (600/300 for 10 minutes etc).
63
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
