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

--
Dennis Kessler
http://www.denniskessler.com/acupuncture

"dk_" wrote in message
...
In article ,
"Fred Smith" wrote:

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

In article ,
"Biff" wrote:

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




"dk_" wrote in message
...
In article ,
"Biff" wrote:

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




"Fred Smith" wrote in message
...
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


"dk_" wrote in message
...

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

--
Dennis Kessler
http://www.denniskessler.com/acupuncture

In article ,
"David Biddulph" wrote:

"dk_" wrote in message
...
In article ,
"Fred Smith" wrote:

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.

-Dennis

--
Dennis Kessler
http://www.denniskessler.com/acupuncture

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


"dk_" wrote in message
...
In article ,
"Biff" wrote:

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




"dk_" wrote in message
...
In article ,
"Biff" wrote:

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




"Fred Smith" wrote in message
...
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


"dk_" wrote in message
...

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

--
Dennis Kessler
http://www.denniskessler.com/acupuncture

In article ,
"Roger Govier" wrote:

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

Rodger,

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

Thanks.

-Dennis




"dk_" wrote in message
...
In article ,
"Biff" wrote:

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




"dk_" wrote in message
...
In article ,
"Biff" wrote:

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




"Fred Smith" wrote in message
...
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


"dk_" wrote in message
...

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

--
Dennis Kessler
http://www.denniskessler.com/acupuncture

"dk_" wrote in message
...
In article ,
"David Biddulph" wrote:

"dk_" wrote in message
...
In article ,
"Fred Smith" wrote:

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

"dk_" wrote in message
...
In article ,
"Roger Govier" wrote:

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

"dk_" wrote in message
...
In article ,
"Biff" wrote:

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




"dk_" wrote in message
...
In article ,
"Biff" wrote:

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




"Fred Smith" wrote in message
...
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


"dk_" wrote in message
...

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

--
Dennis Kessler
http://www.denniskessler.com/acupuncture

In article ,
"David Biddulph" wrote:

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

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_" wrote in message
...
In article ,
"Roger Govier" wrote:

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

"dk_" wrote in message
...
In article ,
"Biff" wrote:

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




"dk_" wrote in message
...
In article ,
"Biff" wrote:

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




"Fred Smith" wrote in message
...
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


"dk_" wrote in message
.
..

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

--
Dennis Kessler
http://www.denniskessler.com/acupuncture

In article ,
"David Biddulph" wrote:

"dk_" wrote in message
...
In article ,
"David Biddulph" wrote:

"dk_" wrote in message
...
In article ,
"Fred Smith" wrote:

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

--
Dennis Kessler
http://www.denniskessler.com/acupuncture

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


"dk_" wrote in message
...
In article ,
"David Biddulph" wrote:

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

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_" wrote in message
...
In article ,
"Roger Govier" wrote:

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

"dk_" wrote in message
...
In article ,
"Biff" wrote:

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




"dk_" wrote in message
...
In article ,
"Biff" wrote:

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




"Fred Smith" wrote in message
...
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


"dk_" wrote in message
.
..

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

--
Dennis Kessler
http://www.denniskessler.com/acupuncture

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

dk_ wrote:
In article ,
"David Biddulph" wrote:

"dk_" wrote in message
...
In article ,
"David Biddulph" wrote:

"dk_" wrote in message
...
In article ,
"Fred Smith" wrote:

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

--
Dennis Kessler
http://www.denniskessler.com/acupuncture

"Pete_UK" wrote:

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

In article ,
"Roger Govier" wrote:

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


"dk_" wrote in message
...
In article ,
"David Biddulph" wrote:

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

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_" wrote in message
...
In article ,
"Roger Govier" wrote:

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

"dk_" wrote in message
...
In article ,
"Biff" wrote:

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




"dk_" wrote in message
...
In article ,
"Biff" wrote:

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




"Fred Smith" wrote in message
...
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


"dk_" wrote in message

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

--
Dennis Kessler
http://www.denniskessler.com/acupuncture






--
Dennis Kessler
http://www.denniskessler.com/acupuncture

In article .com,
"Pete_UK" wrote:

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)



dk_ wrote:
In article ,
"David Biddulph" wrote:

"dk_" wrote in message
...
In article ,
"David Biddulph" wrote:

"dk_" wrote in message
...
In article ,
"Fred Smith" wrote:

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

--
Dennis Kessler
http://www.denniskessler.com/acupuncture




--
Dennis Kessler
http://www.denniskessler.com/acupuncture