|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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,
Have you tried expanding the width of the column that the formula is in??
John
"dk_" <nobody[ at ]spamless.com> wrote in message
news:nobody-9BC117.20573525092006[ at ]sn-radius.vsrv-sjc.supernews.net...
[Quoted Text] > > 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
|
|
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_" <nobody[ at ]spamless.com> wrote in message
news:nobody-9BC117.20573525092006[ at ]sn-radius.vsrv-sjc.supernews.net...
[Quoted Text] > > 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 <#ftN8FS4GHA.2464[ at ]TK2MSFTNGP06.phx.gbl>,
"John Wilson" <jwilson[ at ]optonline.net> wrote:
[Quoted Text] > 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
>
> "dk_" <nobody[ at ]spamless.com> wrote in message
> news:nobody-9BC117.20573525092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> >
> > 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
|
|
|
[Quoted Text] >Excel cannot display negatives times.
Unless you use the 1904 date system but then that opens a giant can of
worms!
Biff
"Fred Smith" <fredsmith99[ at ]yahoo.com> wrote in message
news:O7zuJLS4GHA.1544[ at ]TK2MSFTNGP04.phx.gbl...
> 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_" <nobody[ at ]spamless.com> wrote in message
> news:nobody-9BC117.20573525092006[ at ]sn-radius.vsrv-sjc.supernews.net...
>>
>> 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 <O7zuJLS4GHA.1544[ at ]TK2MSFTNGP04.phx.gbl>,
"Fred Smith" <fredsmith99[ at ]yahoo.com> wrote:
[Quoted Text] > 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
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_" <nobody[ at ]spamless.com> wrote in message
> news:nobody-9BC117.20573525092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> >
> > 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 <uWc7RRS4GHA.1492[ at ]TK2MSFTNGP05.phx.gbl>,
"Biff" <biffinpitt[ at ]comcast.net> wrote:
[Quoted Text] > >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" <fredsmith99[ at ]yahoo.com> wrote in message
> news:O7zuJLS4GHA.1544[ at ]TK2MSFTNGP04.phx.gbl...
> > 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_" <nobody[ at ]spamless.com> wrote in message
> > news:nobody-9BC117.20573525092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> >>
> >> 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
|
|
|
[Quoted Text] >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
"dk_" <nobody[ at ]spamless.com> wrote in message
news:nobody-19C7D3.22315025092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> In article <uWc7RRS4GHA.1492[ at ]TK2MSFTNGP05.phx.gbl>,
> "Biff" <biffinpitt[ at ]comcast.net> 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" <fredsmith99[ at ]yahoo.com> wrote in message
>> news:O7zuJLS4GHA.1544[ at ]TK2MSFTNGP04.phx.gbl...
>> > 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_" <nobody[ at ]spamless.com> wrote in message
>> > news:nobody-9BC117.20573525092006[ at ]sn-radius.vsrv-sjc.supernews.net...
>> >>
>> >> 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
|
|
"dk_" <nobody[ at ]spamless.com> wrote in message
news:nobody-8BA3F6.22300025092006[ at ]sn-radius.vsrv-sjc.supernews.net...
[Quoted Text] > In article <O7zuJLS4GHA.1544[ at ]TK2MSFTNGP04.phx.gbl>,
> "Fred Smith" <fredsmith99[ at ]yahoo.com> 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 <enjaVJT4GHA.2264[ at ]TK2MSFTNGP06.phx.gbl>,
"Biff" <biffinpitt[ at ]comcast.net> wrote:
[Quoted Text] > >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_" <nobody[ at ]spamless.com> wrote in message
> news:nobody-19C7D3.22315025092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> > In article <uWc7RRS4GHA.1492[ at ]TK2MSFTNGP05.phx.gbl>,
> > "Biff" <biffinpitt[ at ]comcast.net> 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" <fredsmith99[ at ]yahoo.com> wrote in message
> >> news:O7zuJLS4GHA.1544[ at ]TK2MSFTNGP04.phx.gbl...
> >> > 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_" <nobody[ at ]spamless.com> wrote in message
> >> > news:nobody-9BC117.20573525092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> >> >>
> >> >> 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 <2ZOdncY8WuWSQIXYnZ2dnUVZ8tOdnZ2d[ at ]bt.com>,
"David Biddulph" <david[ at ]biddulph.org.uk> wrote:
[Quoted Text] > "dk_" <nobody[ at ]spamless.com> wrote in message
> news:nobody-8BA3F6.22300025092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> > In article <O7zuJLS4GHA.1544[ at ]TK2MSFTNGP04.phx.gbl>,
> > "Fred Smith" <fredsmith99[ at ]yahoo.com> 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_" <nobody[ at ]spamless.com> wrote in message
news:nobody-3812E5.02072626092006[ at ]sn-radius.vsrv-sjc.supernews.net...
[Quoted Text] > In article <enjaVJT4GHA.2264[ at ]TK2MSFTNGP06.phx.gbl>, > "Biff" <biffinpitt[ at ]comcast.net> 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_" <nobody[ at ]spamless.com> wrote in message >> news:nobody-19C7D3.22315025092006[ at ]sn-radius.vsrv-sjc.supernews.net... >> > In article <uWc7RRS4GHA.1492[ at ]TK2MSFTNGP05.phx.gbl>, >> > "Biff" <biffinpitt[ at ]comcast.net> 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" <fredsmith99[ at ]yahoo.com> wrote in message >> >> news:O7zuJLS4GHA.1544[ at ]TK2MSFTNGP04.phx.gbl... >> >> > 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_" <nobody[ at ]spamless.com> wrote in message >> >> > news:nobody-9BC117.20573525092006[ at ]sn-radius.vsrv-sjc.supernews.net... >> >> >> >> >> >> 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 <OBSu#zU4GHA.2144[ at ]TK2MSFTNGP04.phx.gbl>,
"Roger Govier" <roger[ at ]technologyNOSPAM4u.co.uk> wrote:
[Quoted Text] > 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_" <nobody[ at ]spamless.com> wrote in message
> news:nobody-3812E5.02072626092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> > In article <enjaVJT4GHA.2264[ at ]TK2MSFTNGP06.phx.gbl>,
> > "Biff" <biffinpitt[ at ]comcast.net> 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_" <nobody[ at ]spamless.com> wrote in message
> >> news:nobody-19C7D3.22315025092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> >> > In article <uWc7RRS4GHA.1492[ at ]TK2MSFTNGP05.phx.gbl>,
> >> > "Biff" <biffinpitt[ at ]comcast.net> 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" <fredsmith99[ at ]yahoo.com> wrote in message
> >> >> news:O7zuJLS4GHA.1544[ at ]TK2MSFTNGP04.phx.gbl...
> >> >> > 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_" <nobody[ at ]spamless.com> wrote in message
> >> >> > news:nobody-9BC117.20573525092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> >> >> >>
> >> >> >> 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_" <nobody[ at ]spamless.com> wrote in message
news:nobody-296CE5.02144126092006[ at ]sn-radius.vsrv-sjc.supernews.net...
[Quoted Text] > In article <2ZOdncY8WuWSQIXYnZ2dnUVZ8tOdnZ2d[ at ]bt.com>,
> "David Biddulph" <david[ at ]biddulph.org.uk> wrote:
>
>> "dk_" <nobody[ at ]spamless.com> wrote in message
>> news:nobody-8BA3F6.22300025092006[ at ]sn-radius.vsrv-sjc.supernews.net...
>> > In article <O7zuJLS4GHA.1544[ at ]TK2MSFTNGP04.phx.gbl>,
>> > "Fred Smith" <fredsmith99[ at ]yahoo.com> 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_" <nobody[ at ]spamless.com> wrote in message
news:nobody-B92362.09340226092006[ at ]sn-radius.vsrv-sjc.supernews.net...
[Quoted Text] > In article <OBSu#zU4GHA.2144[ at ]TK2MSFTNGP04.phx.gbl>,
> "Roger Govier" <roger[ at ]technologyNOSPAM4u.co.uk> 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_" <nobody[ at ]spamless.com> wrote in message
>> news:nobody-3812E5.02072626092006[ at ]sn-radius.vsrv-sjc.supernews.net...
>> > In article <enjaVJT4GHA.2264[ at ]TK2MSFTNGP06.phx.gbl>,
>> > "Biff" <biffinpitt[ at ]comcast.net> 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_" <nobody[ at ]spamless.com> wrote in message
>> >> news:nobody-19C7D3.22315025092006[ at ]sn-radius.vsrv-sjc.supernews.net...
>> >> > In article <uWc7RRS4GHA.1492[ at ]TK2MSFTNGP05.phx.gbl>,
>> >> > "Biff" <biffinpitt[ at ]comcast.net> 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" <fredsmith99[ at ]yahoo.com> wrote in message
>> >> >> news:O7zuJLS4GHA.1544[ at ]TK2MSFTNGP04.phx.gbl...
>> >> >> > 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_" <nobody[ at ]spamless.com> wrote in message
>> >> >> > news:nobody-9BC117.20573525092006[ at ]sn-radius.vsrv-sjc.supernews.net...
>> >> >> >>
>> >> >> >> 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 <4519604c_1[ at ]glkas0286.greenlnk.net>,
"David Biddulph" <david[ at ]biddulph.org.uk> wrote:
[Quoted Text] > 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_" <nobody[ at ]spamless.com> wrote in message
> news:nobody-B92362.09340226092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> > In article <OBSu#zU4GHA.2144[ at ]TK2MSFTNGP04.phx.gbl>,
> > "Roger Govier" <roger[ at ]technologyNOSPAM4u.co.uk> 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_" <nobody[ at ]spamless.com> wrote in message
> >> news:nobody-3812E5.02072626092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> >> > In article <enjaVJT4GHA.2264[ at ]TK2MSFTNGP06.phx.gbl>,
> >> > "Biff" <biffinpitt[ at ]comcast.net> 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_" <nobody[ at ]spamless.com> wrote in message
> >> >> news:nobody-19C7D3.22315025092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> >> >> > In article <uWc7RRS4GHA.1492[ at ]TK2MSFTNGP05.phx.gbl>,
> >> >> > "Biff" <biffinpitt[ at ]comcast.net> 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" <fredsmith99[ at ]yahoo.com> wrote in message
> >> >> >> news:O7zuJLS4GHA.1544[ at ]TK2MSFTNGP04.phx.gbl...
> >> >> >> > 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_" <nobody[ at ]spamless.com> wrote in message
> >> >> >> > news:nobody-9BC117.20573525092006[ at ]sn-radius.vsrv-sjc.supernews.net.
> >> >> >> > ..
> >> >> >> >>
> >> >> >> >> 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 <45195822$1_1[ at ]glkas0286.greenlnk.net>,
"David Biddulph" <david[ at ]biddulph.org.uk> wrote:
[Quoted Text] > "dk_" <nobody[ at ]spamless.com> wrote in message
> news:nobody-296CE5.02144126092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> > In article <2ZOdncY8WuWSQIXYnZ2dnUVZ8tOdnZ2d[ at ]bt.com>,
> > "David Biddulph" <david[ at ]biddulph.org.uk> wrote:
> >
> >> "dk_" <nobody[ at ]spamless.com> wrote in message
> >> news:nobody-8BA3F6.22300025092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> >> > In article <O7zuJLS4GHA.1544[ at ]TK2MSFTNGP04.phx.gbl>,
> >> > "Fred Smith" <fredsmith99[ at ]yahoo.com> 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_" <nobody[ at ]spamless.com> wrote in message
news:nobody-407E49.22250626092006[ at ]sn-radius.vsrv-sjc.supernews.net...
[Quoted Text] > In article <4519604c_1[ at ]glkas0286.greenlnk.net>, > "David Biddulph" <david[ at ]biddulph.org.uk> 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_" <nobody[ at ]spamless.com> wrote in message >> news:nobody-B92362.09340226092006[ at ]sn-radius.vsrv-sjc.supernews.net... >> > In article <OBSu#zU4GHA.2144[ at ]TK2MSFTNGP04.phx.gbl>, >> > "Roger Govier" <roger[ at ]technologyNOSPAM4u.co.uk> 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_" <nobody[ at ]spamless.com> wrote in message >> >> news:nobody-3812E5.02072626092006[ at ]sn-radius.vsrv-sjc.supernews.net... >> >> > In article <enjaVJT4GHA.2264[ at ]TK2MSFTNGP06.phx.gbl>, >> >> > "Biff" <biffinpitt[ at ]comcast.net> 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_" <nobody[ at ]spamless.com> wrote in message >> >> >> news:nobody-19C7D3.22315025092006[ at ]sn-radius.vsrv-sjc.supernews.net... >> >> >> > In article <uWc7RRS4GHA.1492[ at ]TK2MSFTNGP05.phx.gbl>, >> >> >> > "Biff" <biffinpitt[ at ]comcast.net> 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" <fredsmith99[ at ]yahoo.com> wrote in message >> >> >> >> news:O7zuJLS4GHA.1544[ at ]TK2MSFTNGP04.phx.gbl... >> >> >> >> > 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_" <nobody[ at ]spamless.com> wrote in message >> >> >> >> > news:nobody-9BC117.20573525092006[ at ]sn-radius.vsrv-sjc.supernews.net. >> >> >> >> > .. >> >> >> >> >> >> >> >> >> >> 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:
[Quoted Text] > In article <45195822$1_1[ at ]glkas0286.greenlnk.net>, > "David Biddulph" <david[ at ]biddulph.org.uk> wrote: > > > "dk_" <nobody[ at ]spamless.com> wrote in message > > news:nobody-296CE5.02144126092006[ at ]sn-radius.vsrv-sjc.supernews.net... > > > In article <2ZOdncY8WuWSQIXYnZ2dnUVZ8tOdnZ2d[ at ]bt.com>, > > > "David Biddulph" <david[ at ]biddulph.org.uk> wrote: > > > > > >> "dk_" <nobody[ at ]spamless.com> wrote in message > > >> news:nobody-8BA3F6.22300025092006[ at ]sn-radius.vsrv-sjc.supernews.net... > > >> > In article <O7zuJLS4GHA.1544[ at ]TK2MSFTNGP04.phx.gbl>, > > >> > "Fred Smith" <fredsmith99[ at ]yahoo.com> 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:
[Quoted Text] > 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 <OQ5WH#h4GHA.1248[ at ]TK2MSFTNGP03.phx.gbl>,
"Roger Govier" <roger[ at ]technologyNOSPAM4u.co.uk> wrote:
[Quoted Text] > 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_" <nobody[ at ]spamless.com> wrote in message
> news:nobody-407E49.22250626092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> > In article <4519604c_1[ at ]glkas0286.greenlnk.net>,
> > "David Biddulph" <david[ at ]biddulph.org.uk> 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_" <nobody[ at ]spamless.com> wrote in message
> >> news:nobody-B92362.09340226092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> >> > In article <OBSu#zU4GHA.2144[ at ]TK2MSFTNGP04.phx.gbl>,
> >> > "Roger Govier" <roger[ at ]technologyNOSPAM4u.co.uk> 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_" <nobody[ at ]spamless.com> wrote in message
> >> >> news:nobody-3812E5.02072626092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> >> >> > In article <enjaVJT4GHA.2264[ at ]TK2MSFTNGP06.phx.gbl>,
> >> >> > "Biff" <biffinpitt[ at ]comcast.net> 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_" <nobody[ at ]spamless.com> wrote in message
> >> >> >> news:nobody-19C7D3.22315025092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> >> >> >> > In article <uWc7RRS4GHA.1492[ at ]TK2MSFTNGP05.phx.gbl>,
> >> >> >> > "Biff" <biffinpitt[ at ]comcast.net> 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" <fredsmith99[ at ]yahoo.com> wrote in message
> >> >> >> >> news:O7zuJLS4GHA.1544[ at ]TK2MSFTNGP04.phx.gbl...
> >> >> >> >> > 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_" <nobody[ at ]spamless.com> wrote in message
> >> >> >> >> > news:nobody-9BC117.20573525092006[ at ]sn-radius.vsrv-sjc.supernews.n
> >> >> >> >> > 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 <1159354149.732691.321700[ at ]k70g2000cwa.googlegroups.com>,
"Pete_UK" <pashurst[ at ]auditel.net> wrote:
[Quoted Text] > 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 <45195822$1_1[ at ]glkas0286.greenlnk.net>,
> > "David Biddulph" <david[ at ]biddulph.org.uk> wrote:
> >
> > > "dk_" <nobody[ at ]spamless.com> wrote in message
> > > news:nobody-296CE5.02144126092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> > > > In article <2ZOdncY8WuWSQIXYnZ2dnUVZ8tOdnZ2d[ at ]bt.com>,
> > > > "David Biddulph" <david[ at ]biddulph.org.uk> wrote:
> > > >
> > > >> "dk_" <nobody[ at ]spamless.com> wrote in message
> > > >> news:nobody-8BA3F6.22300025092006[ at ]sn-radius.vsrv-sjc.supernews.net...
> > > >> > In article <O7zuJLS4GHA.1544[ at ]TK2MSFTNGP04.phx.gbl>,
> > > >> > "Fred Smith" <fredsmith99[ at ]yahoo.com> 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
|
|
|