Prev: Pasting Spreadsheet into Word
Next: Does anyone have a template for printing L7160 address labels?
From: chieflx on 6 Jul 2008 19:35
Hi,
I wonder if anyone can help with a formula for comparing two times and then
returning a total, in hh:mm based on the result. I have tried to find a
formula on the forum pages that I could adapt but I do not understand logic
formula well enough to do that so I apologise if this has been explained
already but I could not find a formula that seemed to cover this situation.

I am trying to automatically deduct 1 hour if either of the two cells are 8
hours or greater but if neither exceed 7:59 I just want to sum them and
return the result in hh:mm.

I believe that logical it should be something like

=IF time(a) OR time(b) >= 08:00 THEN (time(a)+time(b))-1 ELSE (time(a) +
time(b))

Unfortunately while this works on paper, I can not work out how to put in to
a formula that excel 2003 understands.

The result needs to be in hh:mm format so I can then total the hours for the
month any help would be greatly appreciated.

Many Thanks
Chieflx
From: Dave Peterson on 6 Jul 2008 20:21
Maybe...

=if(or(a1>time(8,0,0),b1>time(8,0,0)),a1+b1-1/24,a1+b1)
=if(or(a1>time(8,0,0),b1>time(8,0,0)),a1+b1-time(1,0,0),a1+b1)
or
=a1+b1-(or(a1>time(8,0,0),b1>time(8,0,0)))/24


1 hour is the same as 1/24 of a day.


chieflx wrote:
>
> Hi,
> I wonder if anyone can help with a formula for comparing two times and then
> returning a total, in hh:mm based on the result. I have tried to find a
> formula on the forum pages that I could adapt but I do not understand logic
> formula well enough to do that so I apologise if this has been explained
> already but I could not find a formula that seemed to cover this situation.
>
> I am trying to automatically deduct 1 hour if either of the two cells are 8
> hours or greater but if neither exceed 7:59 I just want to sum them and
> return the result in hh:mm.
>
> I believe that logical it should be something like
>
> =IF time(a) OR time(b) >= 08:00 THEN (time(a)+time(b))-1 ELSE (time(a) +
> time(b))
>
> Unfortunately while this works on paper, I can not work out how to put in to
> a formula that excel 2003 understands.
>
> The result needs to be in hh:mm format so I can then total the hours for the
> month any help would be greatly appreciated.
>
> Many Thanks
> Chieflx

--

Dave Peterson
From: chieflx on 8 Jul 2008 05:56
Hi Dave,
Thanks for the formula it worked perfectly. I wonder if you could do me a
favour and explain the time(8,0,0,) part of the formula? I realise it must be
a reference to the time but why is in not entered as 08:00?

I think I understand the rest of the formula, I just need to get my head
around the way excel processes the information.

Once again many thanks
chieflx

"Dave Peterson" wrote:

> Maybe...
>
> =if(or(a1>time(8,0,0),b1>time(8,0,0)),a1+b1-1/24,a1+b1)
> =if(or(a1>time(8,0,0),b1>time(8,0,0)),a1+b1-time(1,0,0),a1+b1)
> or
> =a1+b1-(or(a1>time(8,0,0),b1>time(8,0,0)))/24
>
>
> 1 hour is the same as 1/24 of a day.
>
>
> chieflx wrote:
> >
> > Hi,
> > I wonder if anyone can help with a formula for comparing two times and then
> > returning a total, in hh:mm based on the result. I have tried to find a
> > formula on the forum pages that I could adapt but I do not understand logic
> > formula well enough to do that so I apologise if this has been explained
> > already but I could not find a formula that seemed to cover this situation.
> >
> > I am trying to automatically deduct 1 hour if either of the two cells are 8
> > hours or greater but if neither exceed 7:59 I just want to sum them and
> > return the result in hh:mm.
> >
> > I believe that logical it should be something like
> >
> > =IF time(a) OR time(b) >= 08:00 THEN (time(a)+time(b))-1 ELSE (time(a) +
> > time(b))
> >
> > Unfortunately while this works on paper, I can not work out how to put in to
> > a formula that excel 2003 understands.
> >
> > The result needs to be in hh:mm format so I can then total the hours for the
> > month any help would be greatly appreciated.
> >
> > Many Thanks
> > Chieflx
>
> --
>
> Dave Peterson
>
From: Dave Peterson on 8 Jul 2008 09:49
=time() has 3 arguments. Hours, minutes, seconds.

You can read more about it in excel's help.

I don't know why excel won't let you enter:
=if(a1>8:00:00,"after 8","not after 8")

It seems like a pretty reasonable thing to do.

But you could cheat and use:
=if(a1>8/24,"after 8","not after 8")
(8 hours is 8/24th's of a day)

chieflx wrote:
>
> Hi Dave,
> Thanks for the formula it worked perfectly. I wonder if you could do me a
> favour and explain the time(8,0,0,) part of the formula? I realise it must be
> a reference to the time but why is in not entered as 08:00?
>
> I think I understand the rest of the formula, I just need to get my head
> around the way excel processes the information.
>
> Once again many thanks
> chieflx
>
> "Dave Peterson" wrote:
>
> > Maybe...
> >
> > =if(or(a1>time(8,0,0),b1>time(8,0,0)),a1+b1-1/24,a1+b1)
> > =if(or(a1>time(8,0,0),b1>time(8,0,0)),a1+b1-time(1,0,0),a1+b1)
> > or
> > =a1+b1-(or(a1>time(8,0,0),b1>time(8,0,0)))/24
> >
> >
> > 1 hour is the same as 1/24 of a day.
> >
> >
> > chieflx wrote:
> > >
> > > Hi,
> > > I wonder if anyone can help with a formula for comparing two times and then
> > > returning a total, in hh:mm based on the result. I have tried to find a
> > > formula on the forum pages that I could adapt but I do not understand logic
> > > formula well enough to do that so I apologise if this has been explained
> > > already but I could not find a formula that seemed to cover this situation.
> > >
> > > I am trying to automatically deduct 1 hour if either of the two cells are 8
> > > hours or greater but if neither exceed 7:59 I just want to sum them and
> > > return the result in hh:mm.
> > >
> > > I believe that logical it should be something like
> > >
> > > =IF time(a) OR time(b) >= 08:00 THEN (time(a)+time(b))-1 ELSE (time(a) +
> > > time(b))
> > >
> > > Unfortunately while this works on paper, I can not work out how to put in to
> > > a formula that excel 2003 understands.
> > >
> > > The result needs to be in hh:mm format so I can then total the hours for the
> > > month any help would be greatly appreciated.
> > >
> > > Many Thanks
> > > Chieflx
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson
From: "David Biddulph" groups [at] on 8 Jul 2008 11:10
Although it won't allow =if(a1>8:00:00,"after 8","not after 8"), you could
try
=IF(A1>--"8:00:00","after 8","not after 8")
--
David Biddulph

"Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message
news:4873706D.4B94FDEC(a)verizonXSPAM.net...
> =time() has 3 arguments. Hours, minutes, seconds.
>
> You can read more about it in excel's help.
>
> I don't know why excel won't let you enter:
> =if(a1>8:00:00,"after 8","not after 8")
>
> It seems like a pretty reasonable thing to do.
>
> But you could cheat and use:
> =if(a1>8/24,"after 8","not after 8")
> (8 hours is 8/24th's of a day)
>
> chieflx wrote:
>>
>> Hi Dave,
>> Thanks for the formula it worked perfectly. I wonder if you could do me a
>> favour and explain the time(8,0,0,) part of the formula? I realise it
>> must be
>> a reference to the time but why is in not entered as 08:00?
>>
>> I think I understand the rest of the formula, I just need to get my head
>> around the way excel processes the information.
>>
>> Once again many thanks
>> chieflx
>>
>> "Dave Peterson" wrote:
>>
>> > Maybe...
>> >
>> > =if(or(a1>time(8,0,0),b1>time(8,0,0)),a1+b1-1/24,a1+b1)
>> > =if(or(a1>time(8,0,0),b1>time(8,0,0)),a1+b1-time(1,0,0),a1+b1)
>> > or
>> > =a1+b1-(or(a1>time(8,0,0),b1>time(8,0,0)))/24
>> >
>> >
>> > 1 hour is the same as 1/24 of a day.
>> >
>> >
>> > chieflx wrote:
>> > >
>> > > Hi,
>> > > I wonder if anyone can help with a formula for comparing two times
>> > > and then
>> > > returning a total, in hh:mm based on the result. I have tried to find
>> > > a
>> > > formula on the forum pages that I could adapt but I do not understand
>> > > logic
>> > > formula well enough to do that so I apologise if this has been
>> > > explained
>> > > already but I could not find a formula that seemed to cover this
>> > > situation.
>> > >
>> > > I am trying to automatically deduct 1 hour if either of the two cells
>> > > are 8
>> > > hours or greater but if neither exceed 7:59 I just want to sum them
>> > > and
>> > > return the result in hh:mm.
>> > >
>> > > I believe that logical it should be something like
>> > >
>> > > =IF time(a) OR time(b) >= 08:00 THEN (time(a)+time(b))-1 ELSE
>> > > (time(a) +
>> > > time(b))
>> > >
>> > > Unfortunately while this works on paper, I can not work out how to
>> > > put in to
>> > > a formula that excel 2003 understands.
>> > >
>> > > The result needs to be in hh:mm format so I can then total the hours
>> > > for the
>> > > month any help would be greatly appreciated.
>> > >
>> > > Many Thanks
>> > > Chieflx
>> >
>> > --
>> >
>> > Dave Peterson
>> >
>
> --
>
> Dave Peterson


 | 
Pages: 1
Prev: Pasting Spreadsheet into Word
Next: Does anyone have a template for printing L7160 address labels?