Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
calc time value
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
calc time value
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
calc time value
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 - |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
calc time value
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/ kevcar40 wrote in ups.com: 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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calc F9) Button and time. | Excel Discussion (Misc queries) | |||
formula that calc the diff in time, with a less than function? | Excel Worksheet Functions | |||
Time Calc - 24hrs vs 0hrs | Excel Discussion (Misc queries) | |||
time sheet calc by time in and out | Excel Worksheet Functions | |||
Problem with time calc | Excel Worksheet Functions |