time formatting

Hello

I have an issue when creating a column that has some specific time values. For example, I would like the following

3/31/2019 11:00|
3/31/2019 11:15|
3/31/2019 11:20|
...

However, when I tried to write "3/31/2019 11:00" and "3/31/2019 11:15" and do the click-and-drag for the rest of the column, for some reason, the time formatting will work for a while (see tab "rawdata2", Rows 1-101), but then the seconds will start drifting off (see tab "rawdata2", Rows 102-end).

In the tab "rawdata", I used the formula to add 15 minutes to the next cell, as a way to not see the drifting of the seconds, but this creates a secondary problem of not being able to go a particular date that I need when I need to find a date using Ctrl+F.

I would like some help in a solution that will not have drifting seconds, but also allow me to search for a date.

Thanks

If we format column A as Custom m/d/yyyy h:mm:ss.000, we can see what is happening, starting with A4: as we drag down, we are losing 0.005 seconds in each row(!).

Off-hand, I don't why that is happening. I don't believe I've seen that before; but I can duplicate it in a new workbook with my older version of Excel (2010).

I don't have time to investigate this anomaly now. Later tonight. But in general, there is a problem with all methods of "incrementing" time down a column, due to anomalies with 64-bit binary floating-point, which is how Excel represents all numeric values (including time) internally. (I can explain further, if you want.)

The best way to avoid such anomalies is to explicitly round time calculations as follows, starting in A3:

=--TEXT(A2+TIME(0,15,0),"m/d/yyyy h:m")

The double-negate effectively converts text to numeric date and time.

Unfortunately, that is sensitive to how you write dates and time. For example, you might need to change the date format to d/m/yyyy or yyyy/m/d. You might also need to change the time format, although almost every region uses h:m, AFAIK.

PS: To use ctrl+F to find a date and time, be sure click Options > Look In > Values.

Originally Posted by joeu2004

If we format column A as Custom m/d/yyyy h:mm:ss.000, we can see what is happening, starting with A4: as we drag down, we are losing 0.005 seconds in each row(!).
....

I thought this might be the case with Excel's representation of time-value due to me facing it in the past, but maybe because I wasn't doing on 15-minute increments, I didn't see the change that obviously.

However, as you can see in my Excel sheet, I have the formatting set to "m/d/yyyy h:mm", and while visually it looks fine, I see in the actual value, it is actually in the format of "m/d/yyyy h:mm:ss AM/PM."

Is there a discrepancy between me formatting the cells versus the actual value?

Lots of thoughts swirling in my head....

First, no, there is no discrepancy between "formatted value" and "actual value", other than appearance.

I believe you are confusing the value displayed in the cell according to the cell format and the value displayed in the Formula Bar.

They are the same value. But the FB has its own formatting rules, which we cannot modify. The time part, at least, does not seem to be affected by the "Short Time" specification in the Region and Language control panel (in Win 7). In my US version of Excel (2010), it is always h:mm AM/PM in the FB. If you format the cell as hh:mm:ss.000 , you might see more precision.

(Caveat: But if you select the cell, press f2, then Enter, the cell value will be changed to the value displayed in the FB, which is limited to 1-second precision!)

-----

Second, I discovered that the anomaly that I described -- reducing time by 0.005 seconds as we drag A1:A2 down the column -- appears to a "feature" of Excel that is specific to the date/time format.

If we temporarily change the format of A1:A2 to General or Number, drag A1:A2 down the column, then change the format back to m/dd/yyyy hh:mm:ss.000 , there is no 0.005-second difference.

In fact, there is also no 0.005-second difference if we enter the date and time (e.g. 3/31/2019 00:00 and 3/31/2019 00:15), format A1:A2 as hh:mm:ss.000 (that is, hide the date part), drag A1:A2 down the column, then change the format back to m/dd/yyyy hh:mm:ss.000 .

I cannot explain why Excel behaves differently when we drag down cells formatted as m/dd/yyyy hh:mm:ss.000 . It is probably some programmer's "bad idea".

But it is what it is. "Grant me the serenity to accept the things I cannot change / Courage to change the things I can / And wisdom to know the difference."

-----

Nevertheless, despite appearances, even dragging down A1:A2 formatted as General does introduce binary arithmetic anomalies intermittently. They can accumulate; and they can cause problems for some comparison, notably lookup functions like MATCH and VLOOKUP.

So again, it is prudent not to rely on methods like dragging down or fill series.

Instead, enter formulas in one of the following forms, starting in A3:

=--TEXT(A2 + TIME(0,15,0), "m/d/yyyy h:m")
or
=INT(A2 + 15/1440) + TIME(HOUR(A2),MINUTE(A2)+15,0)

I prefer the simplicity of the first form. But the second form eliminates the dependency on local formats of date and time.

15/1440 is 15 minutes stored as Excel time (i.e. 00:15). The TIME function returns only the Excel time part, which is a decimal fraction of 1 (day). For example, if A97 is 3/31/2019 23:45, the formula in A100 returns 00:00, not 1+"00:00". So A2 + 15/1440 is necessary to take care of the case where adding 15 min should cause the date to be incremented.

-----

I have put the cart before the horse. Let's back up and review how Excel stores dates and time.

Dates are stored as integers ("serial numbers"), namely: the number of days since 31 Dec 1899. So 1 Jan 1900 is 1; 2 Jan 1900 is 2; 31 Jan 1900 is 31; 1 Feb 1900 is 32; etc.

You can see the "serial number" by temporarily formatting the cell as General or Number.

Time is stored as a decimal fraction of a day, namely: h/24 + m/1440 + s/86400. "S" might include fractional seconds. But Excel only displays seconds with 1-millisecond precision (s.000).

In Excel, all numbers (including date and time) are stored in 64-bit binary floating-point, a standard numeric representation. Consequently, most decimal fractions (e.g. time) cannot be represented exactly. Moreover, the approximation of a particular decimal fraction varies depending on the magnitude of the number. That is why, for example, IF(10.01 - 10 = 0.01, TRUE) returns FALSE(!).

These approximations of decimal fractions often cause infinitesimal anomalies ("errors") in calculations. Often, we cannot see the infinitesimal differences because Excel formats only the first 15 significant digits (rounded); and Excel formats time only to 1-millisecond precision, as noted above. The point is: the value might be stored with greater precision than we can see.

The work-around is: whenever we expect a calculation to be accurate to some number of decimal places (or unit of time), explicitly round the calculation to that number of decimal places (or unit of time). (Do not round to an arbitrary number of decimal places like 10, as some people suggest.)