Time Formula

[chieflx]

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]

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]

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]

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

 

[David Biddulph]

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