Why does this formula not work?

[dk_]

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

 

[John Wilson]

Dennis,

Have you tried expanding the width of the column that the formula is in??

John

 

[Fred Smith]

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

 

[dk_]

Dennis,

Have you tried expanding the width of the column that the formula is in??

John

Yes. Still shows ####'s. But more importantly it shows a *negative*
number result if I use 'general, or some other 'number' format, other
than a time format.

It'snot the #'s that I wondering about, so much as why the formula works
normally, but not without (E8<D8).

Thanks.

-Dennis

 

[Biff]

Excel cannot display negatives times.

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

Biff

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

 

[dk_]

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

 

[dk_]

Excel cannot display negatives times.

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

Biff[/QUOTE]

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

 

[Biff]

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

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
[/QUOTE]

 

[David Biddulph]

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.

 

[dk_]

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[/QUOTE]


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

 

[dk_]

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.

-Dennis

 

[Roger Govier]

Hi 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

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



[/QUOTE]

 

[dk_]

Hi 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)

Rodger,

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

Thanks.

-Dennis

 

[David Biddulph]

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.

 

[David Biddulph]

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 Biddulph

Rodger,

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

Thanks.

-Dennis

 

[dk_]

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

 

[dk_]

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

 

[Roger Govier]

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


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

 

[Pete_UK]

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

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

 

[Jerry W. Lewis]

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

Excel recognizes any non-zero value as TRUE. Multiplying logical
expressions (AND) will assure the result is 0 or 1, but all bets are off when
when you add (OR) or sutract.

Jerry