Time Formula
 

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

Time Formula
 

Maybe...

=if(or(a1time(8,0,0),b1time(8,0,0)),a1+b1-1/24,a1+b1)
=if(or(a1time(8,0,0),b1time(8,0,0)),a1+b1-time(1,0,0),a1+b1)
or
=a1+b1-(or(a1time(8,0,0),b1time(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

Time Formula
 

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(a1time(8,0,0),b1time(8,0,0)),a1+b1-1/24,a1+b1)
=if(or(a1time(8,0,0),b1time(8,0,0)),a1+b1-time(1,0,0),a1+b1)
or
=a1+b1-(or(a1time(8,0,0),b1time(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

Time Formula
 

=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(a18:00:00,"after 8","not after 8")

It seems like a pretty reasonable thing to do.

But you could cheat and use:
=if(a18/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(a1time(8,0,0),b1time(8,0,0)),a1+b1-1/24,a1+b1)
=if(or(a1time(8,0,0),b1time(8,0,0)),a1+b1-time(1,0,0),a1+b1)
or
=a1+b1-(or(a1time(8,0,0),b1time(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

Time Formula
 

Although it won't allow =if(a18:00:00,"after 8","not after 8"), you could
try
=IF(A1--"8:00:00","after 8","not after 8")
--
David Biddulph

"Dave Peterson" wrote in message
...
=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(a18:00:00,"after 8","not after 8")

It seems like a pretty reasonable thing to do.

But you could cheat and use:
=if(a18/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(a1time(8,0,0),b1time(8,0,0)),a1+b1-1/24,a1+b1)
=if(or(a1time(8,0,0),b1time(8,0,0)),a1+b1-time(1,0,0),a1+b1)
or
=a1+b1-(or(a1time(8,0,0),b1time(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