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

4,510 views

Skip to first unread message

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

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?

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

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.

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

Sep 25, 2019, 2:48:48 PM9/25/19

to

Reply all

Reply to author

Forward

0 new messages

Search

Clear search

Close search

Google apps

Main menu