calc time value

[kevcar40]

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

 

[David M. Marcovitz]

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]

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.

 

[David M. Marcovitz]

If I understand you correctly (and I'm fairly sure I don't), this is what
you want:

=IF(O4="Saturday",IF(R4>$T$25,TEXT(MOD($T$25-Q4,1),"hh:mm:ss"),TEXT(MOD
(R4-Q4,1),"hh:mm:ss")),"")

You said you needed to subtract the end time (in R) from the start time
(in Q), and you said R is already calculated. That threw me off because I
wasn't sure if R is supposed to be the end time or the difference between
the start and end times. I assumed the former in my calculation.

--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/