Two intake records from May 12 belong in one daily total even though one arrived at 08:10 and the other at 16:35. Keep both source times and calculate the grouping key in a new column.

Take the whole-number date portion

With a real numeric spreadsheet date-time in A2, enter the following in B2 and format B2 as a date:

=INT(A2)

Spreadsheet dates in this example use a whole day count with a fraction for time. Formatting A2 as a date can hide the time on screen, while INT in B2 produces a separate numeric day key for grouping.

08:10 and 16:35 on May 12 share a day key while both original times remain visible.

Illustrated grouping. Format the new column as a date.

Try two records around midnight

Check that May 12 23:58 and May 13 00:02 produce different keys. A text string that looks like a timestamp should be parsed and checked before using this formula. If the source times are UTC but the report is by local day, convert the times to the reporting zone before calculating the key.

A late May 12 entry and an early May 13 entry land under different keys.

Illustrated boundary check. Use the day in the timestamp’s intended time zone.

Microsoft’s Excel function reference