Remove Time from Datetime Serials (INT, TRUNC & Pure-Date Matching)

Formatting a date does not remove its time

In the 1900 date system used by this example, March 14, 2026 is serial 46095. A 15:00 timestamp adds 0.625 of a day. Date-only formatting can hide that fraction while equality still compares the full stored values.

Spreadsheet serials do not include a timezone. Normalize timestamps to the intended business timezone before grouping by day. SQL text timestamps, Unix seconds and spreadsheet serials are different representations; INT alone does not convert between them. Excel also supports a 1904 date system, whose corresponding serials differ by 1462 days. Match the source date system before interpreting raw numbers.

Extract the date with INT

H2 reads B3, the 15:00 example, and returns 46095. Format that numeric output as a date for display. The original timestamp is preserved. This exercise accepts positive date serials below 2958466 and rejects missing/text inputs.

=IF(NOT(ISNUMBER(B3)),"Enter numeric timestamp",IF(OR(B3<1,B3>=2958466),"Timestamp outside exercise range",INT(B3)))

Compare TRUNC and INT

H3 gives the same result for these positive serials. INT rounds toward negative infinity; TRUNC removes the fractional part toward zero, so they differ for negative numbers. Negative values are outside this exercise rather than silently interpreted as historical dates.

=IF(NOT(ISNUMBER(B3)),"Enter numeric timestamp",IF(OR(B3<1,B3>=2958466),"Timestamp outside exercise range",TRUNC(B3)))

Extract the time fraction

H4 returns 0.625 from B3. Multiplying by 24 gives 15 hours since midnight; this is a time of day, not an elapsed duration between two events. H5 shows that hour value. No rounding is applied before date matching.

=IF(NOT(ISNUMBER(B3)),"Enter numeric timestamp",IF(OR(B3<1,B3>=2958466),"Timestamp outside exercise range",MOD(B3,1)))

Sum an entire calendar day with a half-open interval

H6 includes timestamps greater than or equal to E2 and strictly less than E2+1. E2 must be a whole-day serial; a timestamp in E2 would otherwise create a shifted 24-hour window. At March 14 the total is 920. March 15 totals 600, including its midnight order.

All four fixed input rows must contain numeric timestamps and amounts; missing data produces a message rather than silently disappearing from the total. Negative amounts are allowed for refunds. A valid date with no transactions returns zero, which can also mean that positive and negative transactions cancel.

=IF(NOT(ISNUMBER(E2)),"Enter numeric target date",IF(OR(E2<1,E2>2958465,E2<>INT(E2)),"Target must be a whole date",IF(COUNT(B2:C5)<>8,"Check timestamp and amount inputs",IF(OR(MIN(B2:B5)<1,MAX(B2:B5)>=2958466),"Timestamp outside exercise range",SUMIFS(C2:C5,B2:B5,">="&E2,B2:B5,"<"&(E2+1))))))

Further reading

• Microsoft Support — INT function
• Microsoft — Date systems in Excel