=b1-a1+(b1<a1)

[Leo Sun]

What is "+(b1+a1)" in this formular for?

Thank you!

 

[Leo Sun]

So sorry, should be "+(b1<a1)" in the formular...

When can i use this feature in Excel functions?

Thank you!

 

[A.W.J. Ales]

Leo,

I assume you mean What is "+(b1<a1)" in this formula for?

From the subject it looks someone helped you with a formula which returns
b1-a1 (if b1 > a1) or with b1-a1+1 (if b1 < a1)

When calculating with a expression which returns TRUE or FALSE ( as b1 < a1
does), that TRUE is "translated" to 1 and that FALSE to 0.

So if b1 < a1 , the expression evaluate to TRUE and the sum b1-a1 is
increased with 1.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Andy Brown]

What is "+(b1+a1)" in this formular for?

When A1 = start time & B1 = end time, B1-A1 calcs the difference. Throwing
in that switch handles if start time is before midnight and end time is
after midnight.

Rgds,
Andy

 

[Bob Phillips]

Hi Leo,

I presume that you mean the +(B1<A1)?

This sis imply checking whether B1<A1 or not. The formula returns True or
False, and the + coerces it to numeric 1 or 0, So if B1<A1, it adds 1, else
it adds 0.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Norman Harker]

Hi Leo!

It's all to do with covering the case where an end time is after
midnight and where we have input only times without a date element.

You can see this better if you format both the start time and end time
as dd-mmm-yyyy hh:mm:ss. In the "abnormal" case of the end time being
earlier than the start time, you'll see they both are on the same day
(01-Jan-1900) whereas, because we have gone over midnight, we should
add 1 day to the end time to make it 02-Jan-1900.

When the start time is less than the end time hours it means that the
end time is in the next day.

To meet this we add 1 in those cases.

=(B1<A1)
returns TRUE if the end time is less than the start time and FALSE
otherwise. In a calculation, the TRUE is coerced to 1 and FALSE to
zero.

If you incorporate dates in the entries of start time and end time the
adjustment isn't necessary.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Leo Sun]

Thank you Ales, Andy, Bob, Norman! Perfect!!

So sorry, should be "+(b1<a1)" in the formular...

When can i use this feature in Excel functions?

Thank you!

 

[Norman Harker]

Hi Leo!

Thanks for thanks. Don't ever hesitate to ask for reasons why or how
something is done.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.