Subtracting hours and minutes with negative times

  • Thread starter Thread starter Nitroman3000
  • Start date Start date
N

Nitroman3000

Hi all,

I created for my self a worksheet where I enter the working time in
morning as well as the working time in the afternoon, I add the sum of
the 2 time and I got the sum of the working time. Now, let's say:

in A1 I have the time I have to work every day: 08:24 / HH:mm
In B1 I have the time I worked on a given day e.g.: 08:30
A1=08:24
B1=08:30
C1= "=IF(B1<A1;A1-B1;B1-A1)=00:06" that is "Today I worked 6 minutes
more".

But if I work less then 8:24 on a day, then the formula gives me an
incorrect result:
A2=08:24
B2=08:05
C2= "=IF(B2<A2;A2-B2;B2-A2)=00:19" that is today I worked 19 minutes
less. I expect "-00:19" minutes, a negative time, indicating that I
worked less.

What am I doing wrong? Any suggestion?
Thank you very much in advance

John
 
in A1 I have the time I have to work every day: 08:24 / HH:mm
In B1 I have the time I worked on a given day e.g.: 08:30
A1=08:24
B1=08:30
C1= "=IF(B1<A1;A1-B1;B1-A1)=00:06" that is "Today I worked 6 minutes
more".

But if I work less then 8:24 on a day, then the formula gives me an
incorrect result:
A2=08:24
B2=08:05
C2= "=IF(B2<A2;A2-B2;B2-A2)=00:19" that is today I worked 19 minutes
less. I expect "-00:19" minutes, a negative time, indicating that I
worked less.

What am I doing wrong?
Click to expand...

Nothing. The IF expression is doing what you told it to do (but
perhaps you do not understand): it always returns non-negative time
-- and for good reason.

Generally, you should compute =B2-A2.

Thus, if actual time worked (B2) is more than expected time worked
(A2), you get a positive result indicating that you worked more.

Likewise, you get a negative result indicating that you worked less.

The problem is: Excel is not happy with negative values using a time
format (Time or Custom [h]:mm). It displays "####" in that case.

To work around that, you always want the result to be non-negative --
which is what your IF expression does.

You only need some mechanism for distinguish "worked more" and "work
less". Exactly what to do depends on your requirements.

One way:

=IF(B2<A2;"-"&TEXT(A2-B2;"[h]:mm");B2-A2)

and you might want to set the Horizontal Alignment format to Right.

The only problem: you will not be able to include that "negative
time" (i.e. negative time __text__) in sums and other arithmetic
operations.

If you are okay with that, fine.

If not, let us know your needs, and we might be able to offer
something that meets your needs.
 
Nitroman3000 said:
Hi all,

I created for my self a worksheet where I enter the working time in
morning as well as the working time in the afternoon, I add the sum of
the 2 time and I got the sum of the working time. Now, let's say:

in A1 I have the time I have to work every day: 08:24 / HH:mm
In B1 I have the time I worked on a given day e.g.: 08:30
A1=08:24
B1=08:30
C1= "=IF(B1<A1;A1-B1;B1-A1)=00:06" that is "Today I worked 6 minutes
more".

But if I work less then 8:24 on a day, then the formula gives me an
incorrect result:
A2=08:24
B2=08:05
C2= "=IF(B2<A2;A2-B2;B2-A2)=00:19" that is today I worked 19 minutes
less. I expect "-00:19" minutes, a negative time, indicating that I
worked less.

What am I doing wrong? Any suggestion?
Thank you very much in advance

John
Click to expand...

Hi John.

There is a little used custom format which allows negative time
under the 1900 date system.
[h]:mm;-[m] "min"

If you dress up B2-A2 with that custom format,
you will still be able to use the result in calculations.

The weakness is that one has to choose hours or minutes (or secs)
to display for negative time.
Only one kind of unit is possible, but in your case the negative time
will probably amount to a smallish number of minutes,
and you may be satisfied with [h]:mm;-[m] "min" or some variation.

Hans T.
 
Back
Top