Subtracting hours and minutes with negative times
Nitroman3000
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
joeu2004
> 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?
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.
Ron Rosenfeld
You don't indicate what wrong answer you are getting. Is it +19 when you expect -19? Or is it a string of ###'s.
In your formula, you are always going to be subtracting the smaller value from the larger because of the IF clause.
If you want to have a negative time result if you work less, you need to always be subtracting the time you are supposed to work (column 1), from the time you actually worked (in column 2).
***HOWEVER*** unless you are using the 1904 date system (standard on Mac's; optional on PC's), Excel will not display the negative time, in time format; rather it will display a string of ###'s
If you are using the 1904 date system, you can just have your formula =B1-A1
If you are using the 1900 date system, you could construct a text string to display the results doing something like:
=TEXT(B1-A1," ;-;")&TEXT(ABS(B1-A1),"hh:mm")
(Note that I use commas for the argument separators; you'll need to change that to semicolons; probably like:
=TEXT(B1-A1;" ;-;")&TEXT(ABS(B1-A1);"hh:mm")
If you need to use these values in a subsequent calculation, and you are using the text "trick" to display the values, i would suggest setting up a helper column where you can have the actual numeric values.
Hans Terkelsen
"Nitroman3000" <gios...@gmail.com> wrote in message news:762a073c-1c10-4cf9...@f36g2000pri.googlegroups.com...
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.