DATETIME() for rounding or truncating to nearest time interval

Version 6

    A nice thing with the DATE() and DATETIME() functions is that they can convert numbers to date and time.

    This makes it relatively easy to calculate dates and times such as rounding or truncating to nearest time interval.

     

    round:DATETIME( INT([DateTimeField]) + (ROUND(FLOAT([DateTimeField]) % 1 * [PartOfDay]) / [PartOfDay]) )
    truncate:DATETIME( INT([DateTimeField]) + (INT(FLOAT([DateTimeField]) % 1 * [PartOfDay]) / [PartOfDay]) )

     

    time interval[PartOfDay][DateTimeField]roundtruncate
    60 min.242016-09-12 21:53:272016-09-12 22:00:002016-09-12 21:00:00
    30 min.482016-09-12 21:53:272016-09-12 22:00:002016-09-12 21:30:00
    20 min.722016-09-12 21:53:272016-09-12 22:00:002016-09-12 21:40:00
    15 min.962016-09-12 21:53:272016-09-12 22:00:002016-09-12 21:45:00
    10 min.1442016-09-12 21:53:272016-09-12 21:50:002016-09-12 21:50:00
      5 min.2882016-09-12 21:53:272016-09-12 21:55:002016-09-12 21:50:00

     

    Related

    How to round up numbers by nearest million?

    DATETIME() for converting numbers to time

    Floor and Ceiling Functions

     

    Attached Workbook Version:  9.0

    .