Excel has a few issues with dates. Mainly they relate to the dates that are imported into Excel. Different systems present different problems. This post solves an issue with dates that include times.

Some systems export a date including the time. This can cause a problem in Excel if you are looking at specific date ranges.

Dates in Excel

Excel treats dates as numbers. The date 1/1/1900 is treated as 1. Every day after that is incremented by one which means the 1/1/2000 is 36526 and 1/1/2016 is 42370.

Times in Excel

Excel treats time as a fraction of a day. So 0.25 is 6AM, 0.5 is noon and 0.75 is 6PM. Which means 42370.75 represents 6PM on 1/1/2016.

When dates are imported with fractions they include a time. This poses a problem when you use <= (less than or equal to) in your logical tests or criteria.

If you use a normal date (no fraction) as your search criteria, then any dates with a fraction will be greater than the normal date with no fraction, hence you will miss out on some dates that have a fraction.

See example below.

On the face of it these dates looks ok.

But if you look at the value in the Formula Bar you can see the problem. This has a 0.5 fraction for noon.

Let’s review a formula that has an issue with these dates.

The formula in D2 is

=COUNTIF(A2:A14,"<="&C2)

This formula should calculate the number of dates in column A4 that are equal to or less than 5/5/16. The value in cell C2 is used by the formula and it does not have a time fraction.

You would expect the formula in cell D2 to return 3. Because the value in cell A4 has a fraction, it is larger than the value in cell C2, so it isn’t counted by the COUNTIF function.

Solutions

There are two solutions. One involves amending the data, the other adjusting the formula.

Adjust the Formula

Because we know there are fractions in the date data we can use a slightly different formula. The formula in D2 that will work with date fractions is

=COUNTIF(A2:A14,"<"&C2+1)

We have removed the = so it is now just less than. We have added one to the day so it is the next day – so in effect we are counting dates less than the next day. This will include fractions of the day involved.

Adjust the Data

If you are going to be doing frequent date calculations, you may want to fix the dates so that they don’t have fractions. The easy way to achieve that is to use a blank column and use a simple formula that removes the fractions from the date numbers.

In cell B2 I will use the following formula

=TRUNC(A2)

The TRUNC function removes fractions. It is short for TRUNCATE which is a computing term to shorten something, or cut off from the end. It is common to remove fractions from numbers in calculations, so there is a function that does it easily.

I can copy this formula down and then use Paste Special Values to paste the results on top of the original date data which replaces the dates in Column A with non-fraction dates. The dates would then be ready to use with normal date calculations.