How to convert a positive duration into a negative in Google Spreadsheet

2
votes

I have to deal with time and duration in Google Spreadsheet App and I have to calculate with negative duration.

Problem: 

--------------------------------------------------------
Begin  |   End    |  Duration  | calculated in negative (for some reasons)
--------------------------------------------------------
08:00  |   14:00  |  06:00     | no
10:00  |   15:00  |  05:00     | yes

If column 'Begin' and 'End' were formatted as "Time", the difference can be easily calculated in the duration column. However converting the duration value into a negative one with a simple solution like(end-begin)*(-1)seems not to be supported.

First solution:

With the following formula I achieved one goal:


    [duration = end - begin]
    (HOUR(duration)*60) + MINUTE(duration))(-1)

I had to convert the duration into minutes, multiply with -1 to convert the number into negative. But this leads to a strange behavior:

--------------------------------------------------------
Begin  |   End    |  Duration   | calculated in negative (for some reasons)
--------------------------------------------------------
08:00  |   14:00  |  06:00      | no
10:00  |   15:00  | -7200:00:00 | yes

So I tried to divide it with 24, 60, 3600, but nothing seems to fit. Until I used the magic number 1440. This number is a multiple of 60, exactly 24 times.

Final solution:


    [duration = end - begin]
    ((HOUR(duration)*60) + MINUTE(duration))(-1))/1440

My questions are:

  1. Does anyone know why to use the number 1440?
  2. Is there another way to solve this problem?
1
timegoogle-sheetsdurationdate-arithmetic
apart from my questions: why do we have to use the code-block button which formats those blocks not the way I wanted? – ptr2n0
The use of code tags are optional. I prefer the use of pre tags but also it's possible to use a four spaces indentation. See stackoverflow.com/editing-help for details. – Rubén
@Rubén Thank you very much. :-) – ptr2n0

1 Answers

1
votes

Google Sheets treat dates and time like serial numbers (same as Excel does):

  • today() is 42 458;
  • tommorow = today() + 1 = 42 459;

each day counts one.

time is the number between 0 and 1. So we have 24 hours in 1, and 60 minutes in 1 hour. Therefore to get duration

  • in minutes: = 24 * 60 = 1440;
  • in seconds = 24 * 60 * 60 = 86 400;