Logical value in time formula?

[geebee2k]

This is a simple question compared to some others here!

I have come across a spreadsheet which does various calculations on time
values. The formula in question is:
=C10+(C10<C5)-C5

....where C5 and C10 are both time values (e.g. 9:30 PM). The formula seeks
to determine the elapsed time between C5 and C10. Obviously, the expression
(C10<C5) is a logical expression, and evaluates to such if it is entered into
a test cell (it evaluates to "TRUE" in every case I tested.) The formula
calculates properly if this term is included, and does NOT calculate properly
if it is NOT included.

I've never used logical expressions in an arithmetic formula. Can anyone
explain why the logical term is necessary, and how it works? I'm just not
comfortable passing the spreadsheet along without understanding how
everything works!

Thanks,
Geebee

 

[Pete_UK]

Suppose your start time is 9:30pm and your finish time is 3:30am (i.e.
the next morning). If you just subtract the start time from the finish
time here then you will get a negative number, because the finish time
is numerically smaller than the start time. To overcome this you need
to add 1 to the result to account for the fact that the finish time is
actually in the next day (Excel stores times as fractions of a 24-hour
day). Hence the need for the logical expression, which returns TRUE or
FALSE which are numerically the same as 1 and 0. Another way of
writing the formula is:

=C10-C5 + IF(C10<C5,1,0)

Hope this helps.

Pete

 

[Joe User]

Can anyone
explain why the logical term is necessary, and how it works?

When TRUE and FALSE are used in an arithmetic expression in Excel, they are
"converted" to their numeric values, 1 and 0 respectively.

=C10+(C10<C5)-C5

To understand the role that "C10<C5" plays, try setting C10 to 9:00 PM and
C5 to 10:00 PM.

Note that the form of the normal expression, C10 - C5, assumes that C5 is
the earlier time. So C5 should be interpreted as 10 PM the previous day.

Thus, the total difference should be 23 hours. But C10 - C5 alone would
result in -1 hours, although Excel does not normally display negative time.
By adding 1 (C10<C5), you are adding 24 hours. So you get 24 - 1 = 23
hours.

You also need to understand that time is stored as a fraction of a day.
That is why the number 1 is interpreted as 24 hours.


----- original message -----

 

[Ms-Exl-Learner]

If you want to get the difference between two times then use text function.

Assume that C5 is the Start Time and C10 is the End Time.
C5 Cell
6:55:22 AM

C10 Cell
5:36:08 PM

=TEXT(C10-C5,"HH:MM:SS")

Myself is a learner so I hope that the experts will guide you about the
explanation you have asked for…

Remember to Click Yes, if this post helps!

 

[David Biddulph]

Using a text function is OK if you just want to display it, but not
necesarily if you want to do calculations.

Some calculations, such as adding or multiplying, will translate the text to
a number, but functions such as SUM and AVERAGE will ignore text.