How to calculate saved or lost time

[MSSailor]

Have defined the cells customized as time h:mm

Let us say that a specified job should take "C19" = 1:20 (h:mm)
Now it takes for someone "B19" = 1:18 (h:mm)
Means minus 2 minutes better than standard.
I write C20=C19-B19 get value not correct

If time is higher than std time then value looks good.

How should I write the formula?

 

[Fred Smith]

"not correct" isn't enough information to troubleshoot your problem. Is
there some reason you couldn't identify the result that you got?

The formula is correct. Check your formatting or ensure the values you have
are times, not text. If you're tying to display a negative time, Excel does
not support that unless you switch to the 1904 date system.

Regards,
Fred.

 

[joeu2004]

Let us say that a specified job should take "C19" = 1:20 (h:mm)
Now it takes for someone "B19" = 1:18 (h:mm)
Means minus 2 minutes better than standard.
I write C20=C19-B19 get value not correct

First, that will result in positive 2 minutes. If you want -2 minutes, you
should compute =B19-C19.

But that will result in "###" unless you format the cell as Number. You
cannot
display negative time in the h:mm format.

If you want the difference to be displayed as integer minutes, not h:mm
whether or not it is negative, try:

=round((B19-C19)*1440,0)

formatted as General or Number.

Explanation: Time (h:mm) is stored as a fraction of a day. So the number
of minutes is, ostensibly, value times hours-per-day times minutes-per-hour
(24*60 = 1440).

You need ROUND because sometimes the arithmetic does not result in an exact
integer due to the way that Excel (like most applications) represents decimal
fractions. For example, with your numbers, B19-C19 is displayed as
-1.9999999999999 when formatted to 15 significant digits, which Excel treats
as different from 2.

 

[MSSailor]

I did as you said and got "#NAME?"

First, that will result in positive 2 minutes. If you want -2 minutes, you
should compute =B19-C19.

But that will result in "###" unless you format the cell as Number. You
cannot
display negative time in the h:mm format.

If you want the difference to be displayed as integer minutes, not h:mm
whether or not it is negative, try:

=round((B19-C19)*1440,0)

formatted as General or Number.

Explanation: Time (h:mm) is stored as a fraction of a day. So the number
of minutes is, ostensibly, value times hours-per-day times minutes-per-hour
(24*60 = 1440).

You need ROUND because sometimes the arithmetic does not result in an exact
integer due to the way that Excel (like most applications) represents decimal
fractions. For example, with your numbers, B19-C19 is displayed as
-1.9999999999999 when formatted to 15 significant digits, which Excel treats
as different from 2.