1. 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.

2. The attachment is missing...

3. [quote name='pieterse' post='769312' date='06-Apr-2009 09:04']The attachment is missing...[/quote]
No, it isn't...
[attachment=83216:x.png]

4. [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

5. 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. [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)?

7. [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.

8. 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. [quote name='HansV' post='769316' date='06-Apr-2009 07:47']No, it isn't...[/quote]Thanks, must have been looking crosseyed.

10. 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. 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
•