Why does this formula not work?

[dk_]

Hi Dennis

I'm not sure my explanation will be exactly accurate, but here goes.

Time is stored as fractions of a day, thus
08:00 = 0.33333333
09:00 = 0.375
02:00 = 0.08333333

If one includes a date as well as time, then 08:00 26 Sep 2006 would be
stored internally as 38987.33333333

If no date is included, and one tries to subtract an "apparently"
earlier from an "apparently" later time, then the result is negative and
is not permitted in Excel under the 1900 date system. If, however, the
"apparently" earlier time belongs to the next 24 hour period, then
adding 1 would be equivalent to adding 1 to the date.

In the example above, 02:00 - 09:00 = 0.08333333 - 0.375 = - 0.29166667
Since negative time is not allowed, add 1 to the result gives = 1 -
0.29166667 = 0.70833333
0.708333333 as a fraction of 24 hours = 17:00 hours

Rodger,

Thank you, thank you for spelling it all out!!!

I see, finally!


I did not have time yet to go through your MOD() explanation. I will,
soon.

Thank you again.

-DK

Using MOD() to achieve the same thing works as follows
=MOD(02:00-09:00,1) = MOD(0.08333333 - 0.375 ,1 ) =MOD(- 0.29166667,1)
= 17

The MOD function can also be expressed as
MOD(n, d) = n - d*INT(n/d)
with n = 02:00 - 09:00 = 0.08333333 - 0.375 = - 0.29166667
and d = 1
INT(n/d)= -1
d*INT(n/d) = -1
- 0.29166667 -1 = 0.708333333 = 17
--
Regards

Roger Govier

Yes, it does work, Dennis. If it's not working for you, then look
again at
the numbers in D8 & E8 and in the answer cell (formatting each of
them as
number, if need be), & tell us what values are there.

If, of course, you've got shifts that last longer than 24 hours, that
method
(and the other simplified suggestions) won't work and you'd need to
put
proper date & time in D8 & E8 & just use E8-D8 formatted as [h]:mm
for the
answer.

David,

My bad. You're right, =MOD(E8-D8,1) does work. I typed my formula
wrong
for testing.

However, not only do I now NOT know why =MOD(E8-D8,1) works, I still
don't understand why adding a "1" using (E8<D8) works. Adding "1" does
add 24 hours to my hour counting., it just makes the hours add in
order
as time goes by.

Thanks.

-Dennis

An alternative to adding 1 (24 hours) to values which are in the
next 24
hour time period, is to use
=MOD(E8-D8,1)

--
Regards

Roger Govier

Rodger,

MOD does not work when the time end crosses the midnight hour.

Thanks.

-Dennis

I don't know why a negative number
is the result of, for example 9:00 PM minus 8:00 PM.

The only thing I can think of is that 8:00 PM is not really
8:00 PM.

Are these times calculated or manually entered? Test the 8:00
PM cell
to see
if it is >1.

Biff


I've got it...

When adding the times together, the simple formula does work, (I
must
have had a typo somewhere), but when the time stamp crosses
midnight,
then I get the ###'s. I see that it is because of a negative
number. I
guess that Excel is reading 1:00 AM the next day, as actually a
lower
number, and then it adds a 1. So when I add the (E8<D8) info,
which
apparently results in 1 (TRUE, I guess), then the time fraction
is a
positive number and everything works. I see that it works, but
it is
confusing me.

I'm confused about totaling the time, because when a '1' is
added, I
would thing that that should represent and additional 24 hours
in
Excel's time counting; but it doesn't.

Thanks.

-Dennis




Excel cannot display negatives times.

Unless you use the 1904 date system but then that opens a
giant
can of
worms!

Biff

I now understand why the ###'s, but I don't know why a
negative
number
is the result of, for example 9:00 PM minus 8:00 PM.

-Dennis




It's because E8 is less than D8. That gives you a negative
number.
Excel
cannot display negatives times. In it's place, you get the
####'s

--
Regards,
Fred


et.
..

Why does this time formula *NOT work* if I leave out
'(E8<D8)'?
The formula works normally when written as below...

=(E8<D8)+E8-D8

When I leave out '(E8<D8)', the cell shows #########
If I use any 'number' format, the number is a negative
number.

The 'Start Time' is in cell D8.
Tne 'End Time' is in cell E8.


The formula works properly even if
the 'End Time' crosses into a new day.

-Dennis

 

[dk_]

The expression (E8<D8) is effectively the same as:

IF(E8<D8,TRUE,FALSE)

when used on its own, so you will only get TRUE or FALSE out of it.
When you combine it with another expression and link them with
arithmetic operations, such as:

=(E8<D8)+E8-D8

then Excel returns a 1 instead of TRUE and a 0 instead of FALSE, so
that these can be used arithmetically. This formula basically means "
... if E8 is less than D8 (i.e. 'appears' to be earlier) then add a 1
onto E8-D8 ... " in order to account for the fact that one time relates
to a different day than the other.

Hope this helps.

Pete

Pete,

It does help! Thank you.

I did not find any explanation for Excel, that Excel sometimes gives you
only TRUE/FALSE in any number format when used WITHOUT another
experssion. I was testing (E8<D8) by itself. ...I thought that it should
have been 1/0.

Your details cleared up the confusion for me.

Thanks for taking the time.

-Dennis (lesson learned)

It's because E8 is less than D8. That gives you a negative number.
Excel
cannot
display negatives times. In it's place, you get the ####'s

E8 is NOT less than D8. D8 is the start time. E8 is the end time. E8
is
a greater number. Why is is computing it to a negative result?

-Dennis

E8 must be less than D8, or else the (E8<D8) term wouldn't make any
difference.

Format D8 and E8 as number, rather than time, and tell us what they
are.

Using the general format, it shows a positive number if the end time is
less than midnight. If the end time is past midnight, then a negative
number is the result, and thus the ###'s in the time formatted cell.

This has me confused, because if I include the (E8<D8) part of the
formula, which I assume add a '1' to the total number, it only changes
the time total like a clock would, rather than adding 24 hours as a
result on adding a '1' in a cell that is formatted with a time style
format. I'm confused.

For cells formatted as date and/or time, Excel counts in units of 24
hours,
so 12:00 has a value of 0.5, 18:00 has a value of 0.75, etc. Adding 1 by
your (E8<D8) term does effectively add 24 hours, to cover the case where
your start time is greater than your finish time so the difference was
coming out negative.

As I said, look at the values in cells D8 and E8 by formatting those
cells
as numbers, then you'll understand what's happening.

Help!

D8 = 8:00 PM (0.83333333)
E8 = 1:00 AM (0.04166667)

H8 = 5 HOURS (0.20833333), when using the formula =(E8<D8)+E8-D8.

What is (E8<D8) doing here?

When I just use '=E8<D8' (in H8), I get TRUE, (when I use any number
format, i.e., general, number, custom [hh],mmm). I don't even get a '1'
or a 'zero'.

Help!!! I'm getting more confused.

-Dennis