Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Loungers....I am returning to a topic that I posted sometime last year (but I can't find the thread)....Don Wells helped me out then, if that provides any clue as to how to find the previous posts on this topic....I need to refine some formulae that I have been using for several months, and have just noticed a glitch. I am attaching some sample data, with a grid on the far right that contains the faulty formulae (I think).

    When people work overtime, they enter dates and times in col J,L,M.......they use a dropdown list to enter an OT code in col N...the 'actual time' shows in col O and those actual times round up or down (to the nearest 1/4) in col Q,R,S (depending on whether it is STO, STM, THO, DTO etc). There is also a combination code (THO; DTO)--the first 2 hours of OT is THO (time-and-a-half); after that, all other overtime is DTO (double) if the person selects the THO;DTO code. The THO;DTO code is supposed to 'look up' the date column and 'see' if there is any THO;DTO on the same day, and if so, then it takes the pre-existing overtime into account and will split any further overtime b/w THO and DTO if the total for that day exceeds 2 hours and if the THO;DTO code is entered.

    There are 2 things that I can't get right---in R24, the rounded up time should be 1.000; in R26, the correct time should be 1.000 (ie: 1 hr from R24 + 1 more hour to top out at 2 hr/day), making the correct time in S26 to be .250 hrs. Right now, R24 is too high, which causes R26 to be too low (b/c there is a 2 hr max/day). As well, the formulae in the grid in col AJ:AY seems to let cells in col J look up only 1 cell above, whereas the cells in col J need to be able to scan all of col J, looking for any day where there is already some previous THO;DTO.....in summary, I think the problem is found in the formulae in col AJ:AY b/c those formulae drive the numbers in col R & S when it comes to calculating the break-out b/w THO and DTO when someone works 2 or more hours of overtime on 1 day. Any help will be appreciated.Thanks.
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The attachment is missing...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='pieterse' post='769312' date='06-Apr-2009 09:04']The attachment is missing...[/quote]
    No, it isn't...
    [attachment=83216:x.png]
    Attached Images Attached Images
    • File Type: png x.png (7.3 KB, 0 views)

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='dmcnab' post='769290' date='05-Apr-2009 16:47']Hi Loungers....I am returning to a topic that I posted sometime last year (but I can't find the thread)[/quote]

    Try: Post 724599
    Regards
    Don

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Don...thanks for that, altho the one you mention is not the post that I had in mind. The one you mention related to auto-sorting data, whereas I am thinking of one that dealt with the entry of the data and splitting the time etc as I have described it inn my post yesterday, when I attached a sample of the data, showing the problem (called WOPR sample.xls), if you wish to look at it

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='dmcnab' post='769332' date='06-Apr-2009 08:30']Hi Don...thanks for that, altho the one you mention is not the post that I had in mind. The one you mention related to auto-sorting data, whereas I am thinking of one that dealt with the entry of the data and splitting the time etc as I have described it inn my post yesterday, when I attached a sample of the data, showing the problem (called WOPR sample.xls), if you wish to look at it[/quote]

    Cell R24 =IF(AND(N24="THO; DTO",O24>0.26),AW24,AO24)
    Should not the AW24 be the minimum of:
    (O24 rounded to the nearest .25) and (AW24)?
    Regards
    Don

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='dmcnab' post='769332' date='06-Apr-2009 08:30']Hi Don...thanks for that, altho the one you mention is not the post that I had in mind. The one you mention related to auto-sorting data, whereas I am thinking of one that dealt with the entry of the data and splitting the time etc as I have described it inn my post yesterday, when I attached a sample of the data, showing the problem (called WOPR sample.xls), if you wish to look at it[/quote]

    Look at the thread which starts here. There are many of the 44 posts in there which deal with formulae.
    Regards
    Don

  8. #8
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Good morning, Don....I am just heading out, but will check that formula and look at what you posted, and get back to you shortly....a bit cool in Ottawa today, eh?

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='769316' date='06-Apr-2009 07:47']No, it isn't...[/quote]Thanks, must have been looking crosseyed.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank you, Don, Hans and others....I was able to 're-construct' from old posts (and Don's present suggestion) and fix the problem that I posted earlier.....very helpful, each of you.....I will do some testing of my new spreadsheet and advise....thanks again

  11. #11
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Good morning, Don & Hans...I am returning to this topic in order to thank each of you for your help.....we have used the new versions of the spreadsheets for about 1 month and so far everything seems to be working as it should....as always, thank you for your help with this....have a good weekend

Posting Permissions

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