Google Sheets: Rounding DateTime Cell to Nearest 15 Mintutes-CodePudding

Is there a formula that I could use to round a date time cell up or down (1/10/2022 15:17:02) to the nearest 15 minute interval? I've tried MROUND but since the date time field is in a weird format, I can't quite figure out how to get that to work.

CodePudding user response：

Seems to work for me, example here: https://docs.google.com/spreadsheets/d/1Hq2pep92vmPQ8kPiThNUod7nsFZopMuOAFNcLCdkJcM/edit?usp=sharing

Using =mround(A2,"00:15") to round, =floor(A2,"00:15") to round down, and =ceiling(A2,"00:15") to round up.

Perhaps you forgot to format the end result back as a Date Time itself?

See more examples here: https://infoinspired.com/google-docs/spreadsheet/round-round-up-round-down-hour-minute-second-in-google-sheets/

CodePudding user response：

you can use round to round down

=round(a1*96)/96

or ceiling to round up

=ceiling(a1*96)/96

with date time fields in Sheets the date is treated like a whole number and the time like a fractional part.

so if you want to round to 15 mins - there are 24 * 4 = 96 lots of 15 minutes in one day. so multiply the date by 96, then round, then divide by 96 should do the trick.