Page 1 of 3 123 LastLast
Results 1 to 15 of 44
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Calculating cumulative time (Excel 2003)

    Hi all...I want to fine tune a formula that I am using to calculate time.I have attached a sample....the background is this:

    If someone works overtime in a day, it is at time-and-a-half (THO) for the first 2 hours and then double-time (DTO) beyond that......in my sample, I have someone working overtime from 7-8:15AM (at THO). They stay late and work overtime from 4:30PM-7PM.....presently, it shows THO for the first 2 hours and then DTO for the last 1/2-hour......you will see there are time calculation formulae in the worksheet as shown, and in columns Y-AE.......the overtime codes in column F normally are on a drop-down but for the moment, they have to be entered manually....anyway, because the person already has 1.25 hrs of THO (from 7-8:15), the overtime b/w 4:30-7:00PM should be .750 of THO (which, with the 1.25 in the morning = 2 hours) and 1.750 of DTO......I have figured out that a new formula which 'picks up' the overtime in the morning will somehow have to take into account the date (found in column but I can't figure out how to work that into the calculations....can someone help me with this? Thanks.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Calculating cumulative time (Excel 2003)

    I am not exactly sure what you need but if If your goal is to subtract the the total of the values in J above the row you can use in J4
    =IF(AND(F4="THO; DTO",G4>0.26),MEDIAN(0.5,2,ROUND(G4*4,0)/4),AD4)-SUM($J$3:J3)

    If your goal is only to sum the Js above if they have the date in column B then use in J4
    =IF(AND(F4="THO; DTO",G4>0.26),MEDIAN(0.5,2,ROUND(G4*4,0)/4),AD4)-SUMIF($B$3:B3,B4,$J$3:J3)

    The DTO in K4 can be calculated simply as:
    =G4-J4

    Copy J4:K4 down the columns
    Steve

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calculating cumulative time (Excel 2003)

    Hi Steve....if, for example, I work on May 1, from 7-8:15AM, and again, on May 1 from 4:30-7PM, the current setup shows 1.25 THO in the morning and 2 THO in the afternoon (and .5 DTO in the afternoon).....but it is only the first two hours of overtime that is THO, so in fact the time starting at 4:30 should be .750 hrs of THO and then 1.75 hrs of DTO (being the time from 5:15-7PM).....currently the formula doesn't take into account the date worked, but (using my sample wkbk) it should look at the date in B5 and if that date is the same as in B4, and if there is a THO code in F4 (the time shown in J4 is 1.25), then the amount of THO in J5 (showing as 2.00) should be reduced by the amount shown in J4 (and the DTO in K5 should be 1.75).....and so long as the dates in column B are not the same (when using a THO or a THO;DTO code) then the THO or THO;DTO calculates as it currently does (ie: the first 2 hrs are THO and the remainder is DTO)......is this any clearer b/c I am having trouble articulating it...??..??...would you like me to do up another sample to thry to illustrate it better?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Calculating cumulative time (Excel 2003)

    This is what I think the calculations should do. Use the 2nd one for the THO and the last one for the DTO.

    Do these work in your real data? If not could you elaborate on the problem?

    Steve

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating cumulative time (Excel 2003)

    I see that you are rounding to the nearest 15 minutes. Is it your intent to have this rounding take place at the final calculation for:
    <UL><LI>each work segment (line)?
    <LI>each day? or
    <LI>the sheet total?[/list]
    Regards
    Don

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating cumulative time (Excel 2003)

    Hello David

    The task became too complicated to handle in one cell, so I did most of the calculating in Columns AH:AP

    The rounding has been done at the work segment level. If you want to do it at the day or sheet level, some rework will be necessary.

    Test out the attachment and let us know how it works out.
    Attached Files Attached Files
    Regards
    Don

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calculating cumulative time (Excel 2003)

    Hi Don....thank you for the attachment....have only looked at it quickly and will do so in greater detail soon, but why does the formula change b/w AN6 and AN7 ?

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating cumulative time (Excel 2003)

    Hi David

    >> why does the formula change b/w AN6 and AN7 ?

    Simply stated: OOPS!! I selected less than I thought when I pasted. The attached copy has corrected that and also deleted a few unnecessary cells at the bottom of the new calculation range.
    Attached Files Attached Files
    Regards
    Don

  9. #9
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calculating cumulative time (Excel 2003)

    Hi Don...I am going to start going through this, but can you tell me of you made any changes to the code in Y4:AE11.....if not, then I won't spend time reviewing that part of the wsheet

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating cumulative time (Excel 2003)

    Good morning David

    >> made any changes to the code in Y4:AE11

    I touched nothing in this area.
    Regards
    Don

  11. #11
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calculating cumulative time (Excel 2003)

    Good morning, Don...the reason I asked is b/c now some of the calculations that formerly worked do not work....I am attaching a smaller version of my real workbook and the area I asked about ( Y4:AE11 ) is actually AJ23:AP30 and you'll see that my entry at J23 produces nothing in R23....(and it should show what is in either AV23 or at least AO23).....??...help ! Thanks
    Attached Files Attached Files

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating cumulative time (Excel 2003)

    Hi David
    The first sample which you provided did not have data validation in the Overtime Code cells (Col F).

    The formulae in cols J and K indicated that they did not expect to find THO in col F but THO; DTO

    I saw no reason to have the user define whether they had progressed from the THO into the DTO category; so I entered THO; DTO into cell F4.

    Recommendation
    Remove the THO entry from the validation list for the overtime code cells.
    Regards
    Don

  13. #13
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calculating cumulative time (Excel 2003)

    ...even having done that, if you look at the attachment to this post (called Sample for Don).xls, you'll see that it no longer splits it into THO and DTO...and using just DTO produces no result at all (see O25)....??..??
    Attached Files Attached Files

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating cumulative time (Excel 2003)

    David
    In migrating the formulae at AH:AP which I provided <!post=here,715180>here<!/post> to your final workbook you managed to lose a column or two For instance, in your final version
    AR23 is =IF(AP23,AQ22,ROW(AP23))
    Should be =IF(AQ23,AR22,ROW(AQ23))

    . I suggest that you re-migrate.
    Regards
    Don

  15. #15
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calculating cumulative time (Excel 2003)

    Hello Don...I am working on that and encountering some strange things but I'm pretty sure that I can figure it out....I'm simply going to retype the formulae, using the actual ranges and see how it works...I will let you know; thank you for your patience and assistance.

Page 1 of 3 123 LastLast

Posting Permissions

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