thanks for reply
sorry for ommiting some important points

ok here we go

Column Q is the time that the occurrence started
Comumn R is the time that the occurence ends
Column R is already calculated
i want to subtract the end occurrence from the start occurrence and
disregard any time occcurred if after 06:00:00
Saturday in column O is checking the day of the week as i only want
this to apply on a saturday morning.




On 31 Oct, 14:52, "David M. Marcovitz" wrote:
I can tell you what's wrong her,b ut I can't tell you how to do it right
because you didn't mention such important things as where you are getting
the difference (is that already calculated in R), what the purpose of
column Q is (you listed it but don't seem to use it at all), and what
Saturday and column O have to do with anything.

The reason why your formula doesn't work is that what's in column R
cannot be simultaneously less than 05:59:59 and greater than 06:00:00
(that's what your AND is asking) so you will always get spit back
whatever is in column R.

--David

--
David M. Marcovitz
Microsoft PowerPoint MVP
Director of Graduate Programs in Educational Technology
Loyola College in Maryland
Author of _Powerful PowerPoint for Educators_http://www.PowerfulPowerPoint.com/

kevcar40 wrote in news:1193841473.628631.271180
@y42g2000hsy.googlegroups.com:



hi
i am trying to calculate theamount o time between 2 time values

s25 = 05:59:59
t25 = 06:00:00

Q R
row 34 04:36:38 04:37:03
row 35 05:16:52 07:14:01

=IF(O34="Saturday",IF(AND(R34$T$25,R34<$S
$25),"06:00:00",TEXT(MOD(R34,1),"hh:mm:ss")),R34) returns 04:37:03




=IF(O35="Saturday",IF(AND(R35$T$25,R35<$S
$25),"06:00:00",TEXT(MOD(R35,1),"hh:mm:ss")),R35) returns
07:14:01

what i want is if the the time value in column R exceeds 06:00:00 the
time is replace with 06:00:00
this works for row 34 but not row 35

any ideas where i going wrong

thanks kevin- Hide quoted text -

- Show quoted text -