Subtracting Time in Numbers

Hi APH,

It is easy, but not until you understand a few of the quirks. Until then, it can be difficult.

20:20 is the time part of a Date and Time value. Even though you entered only the tine part, Numbers stores a full Date and Time value, filling the Date part with the date on which the time part was entered.

In cells where you enter only the date part, Numbers completes the D&T value by setting the time part to 00:00:00 (Midnight at the beginning of that day).

Time, by the way, is always 'time of day' in Numbers. "85 minutes" and "75 minutes" are durations - a measure of how much 'time' has passed. When entering durations, it is useful to use the 1h 20m 10s format, as the 1:20:10 format for the same value is liable to being misunderstood by Numbers and treated as the time part of a D&T value.

If writing a duration into a formula, enclose it in quotation marks (as I've done in the example below) to ensure that Numbers reads it correctly and treats it as a duration.

Example:

I entered 20:30 into A2, creating the D&T value 2020-03-08 8:30:00 PM.

Numbers automatically set the format of the cell to display only the part I had entered, and to display that part exactly as I had entered it.

The formula shown below the table is the one I entered in the selected cell, C2. The one in B2 is identical except for the duration—"85m".

Because I've opened the Formula Editor to display the formula, the Formula Result is shown in the bar at the bottom of the document window. The result is also displayed in the cell containing the formula, C2, but in the cell it is formatted (automatically) to follow the same style as was set by entering the time part only in 24 hour notation, with no seconds.

Regards,

Barry