Why Does Subtracting Two Date Fields Formatted as Yyyy/MM/Dd Result in a Decimal Value?

When two date fields are formatted as yyyy/MM/dd and subtracted using a formula, the result may sometimes include a decimal value. This usually occurs when the user’s browser time zone is observing Daylight Saving Time (DST).

If one of the dates falls within the DST period (typically from March to October) and the other does not (from November to February), subtracting the two fields can result in a decimal.

Example:

The user’s browser is set to the U.S. time zone. The sheet contains two date fields (both formatted as yyyy/MM/dd):

1. A1 = 2020/10/31 (within Daylight Saving Time)

2. A2 = 2020/11/02 (outside Daylight Saving Time)

There is a numeric field A3 with the formula: "A2 - A1".

In this case, A3 will return "2.0416667" instead of the expected "2".

This happens because October 31, 2020, was within DST in the U.S., while November 2, 2020, was not. The difference of "0.0416667" days equals 1 hour.

Solution:

To avoid decimals and return the expected whole number of days, use the formula with the ROUND function: ROUND(A2 - A1)

This rounds the result to the nearest whole number.