Google-sheets – Time duration, when converted to decimal, shows negative

google sheets

enter image description here

I'm trying to compute the difference of two different times to get the following:

  1. Duration of each activity (=C8-B8), then (=E8-D8) and so forth; result will show in C3 and E3 and so forth;

  2. Sum of cells from row 3 will provide Customer Time (B3)
    NOTE: the answer in B3 is multiplied by 24 to get the hours in decimal format and will show in B1

  3. Value in B4 is taken from the Sum of all inputs on row 9 onwards
  4. This will then be computed to get the OB per hour (=B4/B1)

However, the problem is, when getting the duration and it elapsed/crossed a day (ie., 10/27/2015 23:38:00 to 10/28/2015 01:38:00), it's not read (as seen on screenshot, it's saying that the values were considered as text even though I already changed the format to date and time.)

If I remove the date and just put 23:38:00 to 01:38 the results in row 3 will show right duration (as long as format is "time"), but when I get the Sum of customer time to reflect on B3 and multiply by 24 (to get the hour in decimal format), it will say -22.00 (as shown in cell E1)

I tried the formula in excel and it works fine, as long as the dates are entered. But in Google Sheets, aside from there's no shortcut to enter date, the date and time is not considered as such.

Best Answer

Google Sheets does recognize the date-time combination. This is what I get after entering the dates you gave in A1 and B1, and entering =B1-A1 in the cell C1:

+---+---------------------+--------------------+---------------+
|   |          A          |         B          |       C       |
+---+---------------------+--------------------+---------------+
| 1 | 10/27/2015 23:38:00 | 10/28/2015 1:38:00 | 0.08333333334 |
+---+---------------------+--------------------+---------------+

No error; the output is expected: the difference is 0.08333.. of the day, i.e., 2 hours. The column C should be formatted as Duration to make the output user friendly.

Then I tested addition; it also works as expected, with C4 being SUM(C1:C3). 

+---+---------------------+--------------------+----------+
|   |          A          |         B          |    C     |
+---+---------------------+--------------------+----------+
| 1 | 10/27/2015 23:38:00 | 10/28/2015 1:38:00 | 2:00:00  |
| 2 | 10/27/2015 15:38:00 | 10/28/2015 4:38:00 | 13:00:00 |
| 3 | 10/27/2015 14:38:00 | 10/28/2015 2:38:00 | 12:00:00 |
| 4 |                     |                    | 27:00:00 |
+---+---------------------+--------------------+----------+

Conclusion: something went wrong at the stage of entering data in your sheet. Check whether 10/27/2015 is recognized (autoformatted as date) when you enter it in a cell. This behavior depends on your locale setting.

Related Topic