+ Reply to Thread
Results 1 to 4 of 4

help with formula

  1. #1
    Kevin
    Guest

    help with formula

    Hi
    I have a worksheet with column C (start time) & column D (end time).
    (C1:D50)
    e.g
    C D
    07:45 08:50
    11:50 12:50
    In Cells A1 and B1 i have the start break time and end break time
    '08:00' and '08:20' for the first break.
    In Cells A2 and B2 i have the start break time and end break time
    '12:00' and '12:30' for the first break.

    What i want to do is calculate the amount of time lost but exclude
    both break times


    can anyone help?

  2. #2
    Jason Morin
    Guest

    Re: help with formula

    Chip Pearson has a timehseet example with formulas on his
    website:

    http://www.cpearson.com/excel/overtime.htm

    HTH
    Jason
    Atlanta, GA

    >-----Original Message-----
    >Hi
    >I have a worksheet with column C (start time) & column D

    (end time).
    >(C1:D50)
    >e.g
    > C D
    > 07:45 08:50
    > 11:50 12:50
    >In Cells A1 and B1 i have the start break time and end

    break time
    >'08:00' and '08:20' for the first break.
    >In Cells A2 and B2 i have the start break time and end

    break time
    >'12:00' and '12:30' for the first break.
    >
    >What i want to do is calculate the amount of time lost

    but exclude
    >both break times
    >
    >
    >can anyone help?
    >.
    >


  3. #3
    Duke Carey
    Guest

    RE: help with formula

    paste this formula into cell E1

    =60*24*((D1-C1)-IF(AND(D1>$A$1,D1<$B$1),D1-$A$1,0)-IF(AND(C1>$A$1,C1<$B$1),$B$1-C1,0))

    "Kevin" wrote:

    > Hi
    > I have a worksheet with column C (start time) & column D (end time).
    > (C1:D50)
    > e.g
    > C D
    > 07:45 08:50
    > 11:50 12:50
    > In Cells A1 and B1 i have the start break time and end break time
    > '08:00' and '08:20' for the first break.
    > In Cells A2 and B2 i have the start break time and end break time
    > '12:00' and '12:30' for the first break.
    >
    > What i want to do is calculate the amount of time lost but exclude
    > both break times
    >
    >
    > can anyone help?
    >


  4. #4
    Duke Carey
    Guest

    RE: help with formula

    Oops, I just tested that and it fails in the situation where both the start
    time is before 8:00AM and the end date is after 8:20AM. Thus, we need to add
    one more IF() to the formula. Try this one istead

    =60*24*((D1-C1)-IF(AND(D1>$A$1,D1<$B$1),D1-$A$1,0)-IF(AND(C1>$A$1,C1<$B$1),$B$1-C1,0)-IF(AND(C1<$A$1,D1>$B$1),$B$1-$A$1,0))


    "Duke Carey" wrote:

    > paste this formula into cell E1
    >
    > =60*24*((D1-C1)-IF(AND(D1>$A$1,D1<$B$1),D1-$A$1,0)-IF(AND(C1>$A$1,C1<$B$1),$B$1-C1,0))
    >
    > "Kevin" wrote:
    >
    > > Hi
    > > I have a worksheet with column C (start time) & column D (end time).
    > > (C1:D50)
    > > e.g
    > > C D
    > > 07:45 08:50
    > > 11:50 12:50
    > > In Cells A1 and B1 i have the start break time and end break time
    > > '08:00' and '08:20' for the first break.
    > > In Cells A2 and B2 i have the start break time and end break time
    > > '12:00' and '12:30' for the first break.
    > >
    > > What i want to do is calculate the amount of time lost but exclude
    > > both break times
    > >
    > >
    > > can anyone help?
    > >


+ 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