+ Reply to Thread
Results 1 to 2 of 2

Calculate difference in time spanning a day, during office hours o

  1. #1
    frozenfusion
    Guest

    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&(O19>TIME(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, [email protected]


  2. #2
    frozenfusion
    Guest

    RE: Calculate difference in time spanning a day, during office hours o

    have also tried an if and statement

    =IF(AND((P19-N19)>=1,(O19>TIME(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&(O19>TIME(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, [email protected]
    >


    have also tried an if and statement

    =IF(AND((P19-N19)>=1,(O19>TIME(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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1