Often when you import data into Excel the dates may include a time. This can make summarising the data more difficult as time is included as a fraction of a date. You can use an old function to fix the problem. This solution requires the subscription version of Excel.

In the image below you can see a list of dates in column A.

Whilst these look like normal dates, each of them has a time fraction included which you can see displayed in cell A2 below.

The times make it difficult to summarise the data by date.

The formula in the image below in cell E2 should work but it doesn’t because of the time fractions in column A.

We can modify the formula so that the times are removed. This makes summarising by date much easier. You can see the amended formula in cell E2 in the image below.

The formula is.

=SUM($B$2:$B$10*(TRUNC($A$2:$A$10)=D2))

The TRUNC function truncates numbers.

This means it removes fractions from numbers leaving the whole number. It doesn’t do any rounding it simply cuts off any fractions. This means it removes the time and leaves the date. This removal has been applied to the range A2:A10.