I'm trying to compute the difference of two different times to get the following:
Duration of each activity (=C8-B8), then (=E8-D8) and so forth; result will show in C3 and E3 and so forth;
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
- Value in B4 is taken from the Sum of all inputs on row 9 onwards
- 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.