Effective February 22, 2024, Google Groups will no longer support new Usenet content. Posting and subscribing will be disallowed, and new content from Usenet peers will not appear. Viewing and searching of historical data will still be supported as it is done today.
Dismiss

Subtracting hours and minutes with negative times

4,510 views
Skip to first unread message

Nitroman3000

unread,
Feb 19, 2011, 10:47:50 PM2/19/11
to
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

joeu2004

unread,
Feb 19, 2011, 11:35:28 PM2/19/11
to
On Feb 19, 7:47 pm, Nitroman3000 <giosu...@gmail.com> wrote:
> 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

unread,
Feb 19, 2011, 11:40:00 PM2/19/11
to

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

unread,
Feb 20, 2011, 5:23:31 AM2/20/11
to

"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.

Message has been deleted

gliv...@gmail.com

unread,
Sep 25, 2019, 2:48:48 PM9/25/19
to
Thanks Ron ! This helps me to find the difference of timings between stations in railways.
Reply all
Reply to author
Forward
0 new messages