Results 1 to 15 of 23
Thread: Calculating time (Excel 2003)

20080808, 01:17 #1
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Calculating time (Excel 2003)
Good morning, loungers.....I am looking for some help with a time calculation formula....in a manner of speaking, I want to 'have my cake and eat it too' b/c I want a formula that will give two different types of functionality to the same cell....I attach a zip file with a txt doc in it that explains in more detail what I am searching for, as well as a sample xls file.....any suggestions are greatly appreciated...as always, thank you.

20080808, 08:05 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Calculating time (Excel 2003)
What do you want to trigger it being "actual time"?
You use half/full time if it is GIP/GIF (among others) and actual with any other. Do you want it actual if the time is < some value or under what criteria?
Steve

20080808, 13:17 #3
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Calculating time (Excel 2003)
..hhmmm....what about if the 'rule' is as it currently shows, but the exception to the rule would be if the actual time was b/w 3.5 & 3.75 it would show as 3.625...so that anything up to 3.5 is presumed to be less than a half day...and anything b/w 3.53.75 = a half day (3.625)......and any period of time in excess of 3.75 would be presumed to be a full day.......would that work, and still permit the GIF and GIP codes to function like the other similar codes (eg: use them for multiple days etc) ?
I also thought about making up a 'new' code (eg: GIF.1) that calculated in actual time using something like that....

20080809, 12:54 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Calculating time (Excel 2003)
How about:
=IF(AND(MIN((MAX(B23+(E23<D23),C23)B23+(E23>D23))*(E23D23+(E23<D23))*24,7.25)<3.5,OR($F23="gif",$F23="gi p")),MIN((MAX(B23+(E23<D23),C23)B23+(E23>D23))*(E23D23+(E23<D23))*24,7.25),IF(OR($F23="awr",$F23="ccp ",$F23="cip",$F23="ciw",$F23="gif",$F23="gip",$F23 ="ltdi",$F23="mat",$F23="mhp",$F23="vac",$F23="vsk "),(MAX(B23+(E23<D23),C23)B23+(E23>D23))*CEILING((E23D23+(E23<D23)"1:00")*24,3.625),MIN((MAX(B23+(E23<D23),C23)B23+(E23>D23))*(E23D23+(E23<D23))*24,7.25)))
Or this is a little shorter
=IF(OR(AND($F23<>"awr",$F23<>"ccp",$F23<>"cip",$F2 3<>"ciw",$F23<>"gif",$F23<>"gip",$F23<>"ltdi",$F23 <>"mat",$F23<>"mhp",$F23<>"vac",$F23<>"vsk"),AND(M IN((MAX(B23+(E23<D23),C23)B23+(E23>D23))*(E23D23+(E23<D23))*24,7.25)<3.5,OR($F23="gif",$F23="gi p"))),MIN((MAX(B23+(E23<D23),C23)B23+(E23>D23))*(E23D23+(E23<D23))*24,7.25),(MAX(B23+(E23<D23),C23)B23+(E23>D23))*CEILING((E23D23+(E23<D23)"1:00")*24,3.625))
Or even this:
=IF(OR(ISNA(MATCH($F23,{"awr","ccp","cip","ciw","g if","gip","ltdi","mat","mhp","vac","vsk"},0)),AND( MIN((MAX(B23+(E23<D23),C23)B23+(E23>D23))*(E23D23+(E23<D23))*24,7.25)<3.5,OR($F23="gif",$F23="gi p"))),MIN((MAX(B23+(E23<D23),C23)B23+(E23>D23))*(E23D23+(E23<D23))*24,7.25),(MAX(B23+(E23<D23),C23)B23+(E23>D23))*CEILING((E23D23+(E23<D23)"1:00")*24,3.625))
If you wanted to exclude "blp" in actual time formula (it is in the list as a half/full time, but not in your formula) you could use:
=IF(OR(ISNA(MATCH($F23,List!$K$17:$K$28,0)),AND(MI N((MAX(B23+(E23<D23),C23)B23+(E23>D23))*(E23D23+(E23<D23))*24,7.25)<3.5,OR($F23="gif",$F23="gi p"))),MIN((MAX(B23+(E23<D23),C23)B23+(E23>D23))*(E23D23+(E23<D23))*24,7.25),(MAX(B23+(E23<D23),C23)B23+(E23>D23))*CEILING((E23D23+(E23<D23)"1:00")*24,3.625))
Steve

20080809, 13:39 #5
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Calculating time (Excel 2003)
Whew...!...those are some pretty complex looking formulae, but so far they seem to be passing the test (at least, my testing.....)........I have a couple of followup questions:
Which one would you recommend?
How should the rounding formulae in column H be changed? The formulae in column H exclude the 1/2day,fullday codes from being rounded b/c ordinarily they max out in column G at either 3.625 or 7.25. I removed the GIF & the GIP codes from the formulae in column H, but then an 'actual time' entry doesn't round the way it is supposed to.....if I use a time of 8:1512:00, it maxes out at 3.625 in column G (which is what I want) but then rounds up to 3.750 in column H (the number that is the one relied on for accounting purposes)....would I be better to make all of these codes (from the 1/2day, fullday list) capable of calculating time in either manner? Will that have too many IF functions nested together?
Is my explanation clear enough?

20080810, 02:27 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Calculating time (Excel 2003)
I would recommend the last one as the simplest. It also has the advantage that you can change teh ones to calculate with "nonactual" by just modifying the codes in the range.
You could change the rounding formula to include the extra bit with the GIG/GIP and the total which is in the new formula or include the rounding in the 2 segments of the larger one. I haven't broken down the logic of what you are trying to do, you might try to reduce the complexity of the 2 types of calculations.
I am not a fan of "megaformulas". Often I find creating intermediate columns of the results and having the formula choose which one, is easier to maintain and troubleshoot since the logic is more straightforward. The formulas would also become much simpler if you had intermediate calcs of the times and included the dates in them. Then you can subtract directly.
Steve

20080810, 02:45 #7
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Calculating time (Excel 2003)
Hi Steve....by "the last one" are you referring to the formula that is prefaced with "If you want to exclude "blp" in actual time formula...." (the 4th formula in your post?)?
And, I must confess that I'm not sure what you mean in your second paragraph when you say that I could "...change the rounding formula to include the extra bit with the GIG/GIP and the total which is in the new formula or include the rounding in the 2 segments of the larger one.".....

20080810, 06:28 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Calculating time (Excel 2003)
Yes I was referring to the shortest formula.
In the 2nd part I was refering to your use of 2 formulas when the rounding could include the:
AND(MIN((MAX(B23+(E23<D23),C23)B23+(E23>D23))*(E23D23+(E23<D23))*24,7.25)<3.5,OR($F23="gif",$F23="gi p"))
Part in the rounding if desired which tests for the time <3.5 and whether gif or gip....
The other option is to make a "more megaformula" that also includes the rounding... in the 2 sections [But as I said I would recommend a more intermediates and "less Mega")
Steve

20080810, 08:47 #9
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Calculating time (Excel 2003)
Hi Steve....I am using the 4th variation of formulae that you suggested...and I have incorporated part of it into the rounding formula in column H, but it is now not even doing any rounding, in some cases: see attached sample, espec on the 9th...8:159:10; APPT; .917hr; rounds to 0...??...??

20080810, 11:04 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Calculating time (Excel 2003)
I don't understand why you have different rounding formulas
In the formulas in H23:H26, none are rounded since they are all part of your "list" which do not get rounded
In H27:H31, only the APPT is "rounded", but your formula is not rounding the number. If you are calculating actual time (the items are not in the list) then the part of your new formula:
AND(MIN((MAX(B29+(E29<D29),C29)B29+(E29>D29))*(E29D29+(E29<D29))*24,7.25)<3.5,OR($F29="gif",$F29="gi p"))
Will always be FALSE since GIP/GIF are on the list) which = 0 so you will always get zero as a result when you round it.
I suspect that this may be what you are after based on what you ask:
=IF(OR(ISNA(MATCH($F23,List!$K$17:$K$28,0)),AND(MI N((MAX(B23+(E23<D23),C23)B23+(E23>D23))*(E23D23+(E23<D23))*24,7.25)<3.5,OR($F23="gif",$F23="gi p"))),ROUND(G23*4,0)/4, G23)
If the "absence code" is not in the list OR the absence code is GIP or GIF and the time is <3.5, then round it to the nearest 15 minutes, otherwise do not round it.
[Note: Except that it rounds differently in H23 and H28 since your "should be" values are not rounded correctly. 2.117 is closer to 2.000 than 2.250 and 1.833 is closer to 1.750 than to 2.000. ]
Steve

20080918, 11:29 #11
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Calculating time (Excel 2003)
Hi Steve....you may recall this topic from back in August....can you suggest something re: an OR formula. I am attaching a sample of the spreadsheet that I have been working on...I want to add one more little touch to it. There are some absence codes that calculate as 1/2day or fullday (and round to 3.625 or 7.25 hours respectively), such as MAT or VAC b/c people generally take VAC or MAT leave in 1/2 or full day portions. Other absence codes calculate in 'actual time'. "Part time" (PTime) has to calculate as actual time b/c working from 8:1512:00 = 3.75.....it can't round to 3.625 (b/c the person would be underpaid), nor could working 13:0016:30 round to 3.625 b/c they would be overpaid).......I am looking for a way to permit PTime to calculate in actual time, but still do it for multiple, consecutive days like the MAT, VAC ect codes.....I have explained it more, with examples on the attachment.....thank you, as always...and if anyone else watching this topic has a suggestion, all suggestions are welcome...

20080919, 11:39 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Calculating time (Excel 2003)
Wouldn't adding it to the OR calculate it in "real time"?
G23:
=IF(OR($F23="Ptime",$F23="ccp",$F23="cip",$F23="ciw",$F23="ltd", $F23="mat",$F23="vac",$F23="vsk"),(MAX(B23+(E23<D2 3),C23)B23+(E23>D23))*CEILING((E23D23+(E23<D23)"1:00")*24,3.625),MIN((MAX(B23+(E23<D23),C23)B23+(E23>D23))*(E23D23+(E23<D23))*24,7.25))
H23
=IF(OR($F23="Ptime",$F23="ccp",$F23="cip",$F23="ciw",$F23="ltd", $F23="mat",$F23="vac",$F23="vsk"),G23,ROUND(G23*4, 0)/4)
If that does not work, could you elaborate perhaps attaching an example with some that work and those that don't work showing what the formula calculates and indicating what it should be and how that number is derived?
Steve

20080920, 07:43 #13
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Calculating time (Excel 2003)
Hi Steve...the codes "ccp","cip","ciw","ltd","mat","vac","vsk" are set up to calculate a 'morning' as 3.625hrs and an afternoon as 3.625hrs (which equals a full day of 7.25) b/c when people take VAC or MAT, they do so in 1/2 or fullday blocks of time.............if PTime were added to that list, someone working from 8:1512:00 as PT would only get paid for 3.625hrs even though they worked 3.75...and someone working from 13:0016:30 (3.5hrs) would be overpaid at 3.625........the PTime code works properly as is, but I am trying to make it more convenient to enter the time worked by permitting it to calculate time for multiple, consecutive days (like VAC, VSK, CCP etc etc).....I have attached an updated sample that better illustrates what I am shooing for.......if I can make it work (ie: turn PTime into some sort of 'hybrid' code that calculate in actual time (instead of the 3.625 = half day method) and allows someone to enter time for a series of multiple, consec days (instead of having to enter each day) that would be perfect.....thanks for helping me with this.

20080920, 08:15 #14
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Calculating time (Excel 2003)
If a user enters something incorrectly I think you need to increase the training to ensure that they enter things correctly. If you want the formula to account for incorrect entries, could you list all the incorrect ways that you want to account for when they make an entry and what is incorrect about them. Show the entry with the calculated value and list what the correct value is. This way we can try and make the formula account for the errors they make in entering the values. [In the current entry what is wrong, what should the result be, and what is the logic?
You only list one value that it calculated incorrectly, but you don't indicate what the correct value is or what the logic is to get that value.
Steve

20080920, 08:29 #15
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Calculating time (Excel 2003)
In the sample that I attached to my last post, the entries in row 28 are incorrect b/c the GIP code is not a 1/2day, fullday code (see codes at top of that worksheet); the correct way to enter those events would be a separate entry for the 6th, the 7th and the 8th. In row 32, if PTime were a multiple, consecutive day code, the correct result in G32 would be 36.250 hrs (5 days @ 7.25=36.250)