=b1-a1+(b1<a1)

  • Thread starter Thread starter Leo Sun
  • Start date Start date
So sorry, should be "+(b1<a1)" in the formular...

When can i use this feature in Excel functions?

Thank you!
 
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 *
 
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
 
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)
 
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.
 
Thank you Ales, Andy, Bob, Norman! Perfect!!


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

When can i use this feature in Excel functions?

Thank you!
Click to expand...
 
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.
 
Back
Top