Results 1 to 15 of 44

20080608, 19:37 #1
 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 timeandahalf (THO) for the first 2 hours and then doubletime (DTO) beyond that......in my sample, I have someone working overtime from 78:15AM (at THO). They stay late and work overtime from 4:30PM7PM.....presently, it shows THO for the first 2 hours and then DTO for the last 1/2hour......you will see there are time calculation formulae in the worksheet as shown, and in columns YAE.......the overtime codes in column F normally are on a dropdown but for the moment, they have to be entered manually....anyway, because the person already has 1.25 hrs of THO (from 78:15), the overtime b/w 4:307: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.

20080609, 01:09 #2
 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:
=G4J4
Copy J4:K4 down the columns
Steve

20080609, 01:54 #3
 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 78:15AM, and again, on May 1 from 4:307PM, 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:157PM).....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?

20080609, 12:54 #4
 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

20080609, 20:35 #5
 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

20080609, 23:03 #6
 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.Regards
Don

20080610, 00:50 #7
 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 ?

20080610, 01:28 #8
 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.Regards
Don

20080610, 04:17 #9
 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

20080610, 14:52 #10
 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

20080610, 18:13 #11
 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

20080610, 20:07 #12
 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

20080610, 20:18 #13
 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)....??..??

20080610, 21:23 #14
 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 remigrate.Regards
Don

20080611, 01:09 #15
 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.