| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate difference in time spanning a day, during office hours o
i'm trying to get the difference in times spanning a day during office hours
ie.from 6/9/2007 10:35am till 7/9/2007 9:45am, excluding time between 6/9/2007 5:00pm and 7/9/2007 7:30 am.Here is where i'm stuck... if the start time is after 5:00pm 6/9/2007 only calculate from 7/9/2007 7:30am till 9:45am this is what i have so far, replace "date/time" with cell number "Logical if" if ("date out"-"date in")=1, "value if true" (time(17,0,0)-"time in")+("time out"-time(7,30,0), "value if false" ("time out"-"time in") i can't figure out how to tell it if ("time in"time(17,0,0)) then it must just ("time out"-time(7,30,0)) and not the whole value if true statement, and still keep the whole thing... =IF((P19-N19)=1&(O19TIME(17,0,0)),(TIME(17,0,0)-O19)+(Q19-TIME(7,30,0)),(Q19-O19)) <-----produces negative result ############# example of cells date in time in Date Out time out time Diff (23/08/2005 16:30:00 24/08/2005 08:30:00 1:30:00) works what i need, but still keeping the above working 23/08/2005 17:30:00 24/08/2005 08:00:00 0:30:00 if you can help, please mail, |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
calculate difference in time spanning a day, during office hours o
I have also tried an if and statement
=IF(AND((P19-N19)=1,(O19TIME(17,0,0))),(Q19-TIME(7,30,0)),(Q19-O19)), IF(AND(P19-N19)=1,(TIME(17,0,0)-O19)+(Q19-TIME(7,30,0)),(Q19-O19)<---produces #Value! "frozenfusion" wrote: i'm trying to get the difference in times spanning a day during office hours ie.from 6/9/2007 10:35am till 7/9/2007 9:45am, excluding time between 6/9/2007 5:00pm and 7/9/2007 7:30 am.Here is where i'm stuck... if the start time is after 5:00pm 6/9/2007 only calculate from 7/9/2007 7:30am till 9:45am this is what i have so far, replace "date/time" with cell number "Logical if" if ("date out"-"date in")=1, "value if true" (time(17,0,0)-"time in")+("time out"-time(7,30,0), "value if false" ("time out"-"time in") i can't figure out how to tell it if ("time in"time(17,0,0)) then it must just ("time out"-time(7,30,0)) and not the whole value if true statement, and still keep the whole thing... =IF((P19-N19)=1&(O19TIME(17,0,0)),(TIME(17,0,0)-O19)+(Q19-TIME(7,30,0)),(Q19-O19)) <-----produces negative result ############# example of cells date in time in Date Out time out time Diff (23/08/2005 16:30:00 24/08/2005 08:30:00 1:30:00) works what i need, but still keeping the above working 23/08/2005 17:30:00 24/08/2005 08:00:00 0:30:00 if you can help, please mail, |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
calculate difference in time spanning a day, during office hours o
You need an IF, IF statement
"Logical if" if1 ("date out"-"date in")=1, "value if1 true" If2("time in"time(17,0,0)) "value if2 true" ("time in"time(17,0,0)) "value if2 false" (time(17,0,0)-"time in")+("time out"-time(7,30,0) "value if1 false" ("time out"-"time in") Mike F "frozenfusion" wrote in message ... I have also tried an if and statement =IF(AND((P19-N19)=1,(O19TIME(17,0,0))),(Q19-TIME(7,30,0)),(Q19-O19)), IF(AND(P19-N19)=1,(TIME(17,0,0)-O19)+(Q19-TIME(7,30,0)),(Q19-O19)<---produces #Value! "frozenfusion" wrote: i'm trying to get the difference in times spanning a day during office hours ie.from 6/9/2007 10:35am till 7/9/2007 9:45am, excluding time between 6/9/2007 5:00pm and 7/9/2007 7:30 am.Here is where i'm stuck... if the start time is after 5:00pm 6/9/2007 only calculate from 7/9/2007 7:30am till 9:45am this is what i have so far, replace "date/time" with cell number "Logical if" if ("date out"-"date in")=1, "value if true" (time(17,0,0)-"time in")+("time out"-time(7,30,0), "value if false" ("time out"-"time in") i can't figure out how to tell it if ("time in"time(17,0,0)) then it must just ("time out"-time(7,30,0)) and not the whole value if true statement, and still keep the whole thing... =IF((P19-N19)=1&(O19TIME(17,0,0)),(TIME(17,0,0)-O19)+(Q19-TIME(7,30,0)),(Q19-O19)) <-----produces negative result ############# example of cells date in time in Date Out time out time Diff (23/08/2005 16:30:00 24/08/2005 08:30:00 1:30:00) works what i need, but still keeping the above working 23/08/2005 17:30:00 24/08/2005 08:00:00 0:30:00 if you can help, please mail, |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
calculate difference in time spanning a day, during office hours o
http://www.cpearson.com/excel/DateTimeWS.htm
Chip Pearson's page on this topic. -- Regards, Tom Ogilvy "frozenfusion" wrote in message ... I have also tried an if and statement =IF(AND((P19-N19)=1,(O19TIME(17,0,0))),(Q19-TIME(7,30,0)),(Q19-O19)), IF(AND(P19-N19)=1,(TIME(17,0,0)-O19)+(Q19-TIME(7,30,0)),(Q19-O19)<---produc es #Value! "frozenfusion" wrote: i'm trying to get the difference in times spanning a day during office hours ie.from 6/9/2007 10:35am till 7/9/2007 9:45am, excluding time between 6/9/2007 5:00pm and 7/9/2007 7:30 am.Here is where i'm stuck... if the start time is after 5:00pm 6/9/2007 only calculate from 7/9/2007 7:30am till 9:45am this is what i have so far, replace "date/time" with cell number "Logical if" if ("date out"-"date in")=1, "value if true" (time(17,0,0)-"time in")+("time out"-time(7,30,0), "value if false" ("time out"-"time in") i can't figure out how to tell it if ("time in"time(17,0,0)) then it must just ("time out"-time(7,30,0)) and not the whole value if true statement, and still keep the whole thing... =IF((P19-N19)=1&(O19TIME(17,0,0)),(TIME(17,0,0)-O19)+(Q19-TIME(7,30,0)),(Q 19-O19)) <-----produces negative result ############# example of cells date in time in Date Out time out time Diff (23/08/2005 16:30:00 24/08/2005 08:30:00 1:30:00) works what i need, but still keeping the above working 23/08/2005 17:30:00 24/08/2005 08:00:00 0:30:00 if you can help, please mail, |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
calculate difference in time spanning a day, during office hou
Thanks Tom and Mike
I'll have a go at it, and post back if it works "Tom Ogilvy" wrote: http://www.cpearson.com/excel/DateTimeWS.htm Chip Pearson's page on this topic. -- Regards, Tom Ogilvy "frozenfusion" wrote in message ... I have also tried an if and statement =IF(AND((P19-N19)=1,(O19TIME(17,0,0))),(Q19-TIME(7,30,0)),(Q19-O19)), IF(AND(P19-N19)=1,(TIME(17,0,0)-O19)+(Q19-TIME(7,30,0)),(Q19-O19)<---produc es #Value! "frozenfusion" wrote: i'm trying to get the difference in times spanning a day during office hours ie.from 6/9/2007 10:35am till 7/9/2007 9:45am, excluding time between 6/9/2007 5:00pm and 7/9/2007 7:30 am.Here is where i'm stuck... if the start time is after 5:00pm 6/9/2007 only calculate from 7/9/2007 7:30am till 9:45am this is what i have so far, replace "date/time" with cell number "Logical if" if ("date out"-"date in")=1, "value if true" (time(17,0,0)-"time in")+("time out"-time(7,30,0), "value if false" ("time out"-"time in") i can't figure out how to tell it if ("time in"time(17,0,0)) then it must just ("time out"-time(7,30,0)) and not the whole value if true statement, and still keep the whole thing... =IF((P19-N19)=1&(O19TIME(17,0,0)),(TIME(17,0,0)-O19)+(Q19-TIME(7,30,0)),(Q 19-O19)) <-----produces negative result ############# example of cells date in time in Date Out time out time Diff (23/08/2005 16:30:00 24/08/2005 08:30:00 1:30:00) works what i need, but still keeping the above working 23/08/2005 17:30:00 24/08/2005 08:00:00 0:30:00 if you can help, please mail, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Time difference from 24 hours | Excel Discussion (Misc queries) | |||
| find the difference between start time and end time when spanning. | Excel Discussion (Misc queries) | |||
| Calculate difference in time spanning a day, during office hours o | Excel Discussion (Misc queries) | |||
| calculate difference in time to hours | Excel Worksheet Functions | |||
| How do I calculate difference in days & hours between two dates e. | Excel Worksheet Functions | |||
Linear Mode
