If than else ?? or something else ?

[joesanchez]

I've been racking my brain on this one and I hope someone can help me out before I pop a blood vessel. We track the sickdays and leave early or come to work late occurances. the are given a point or a 1/2 point depending on the amount of time. problem is with the point status formula. I cant figure it out. the employee gains back a 1/2 point every 60 days . I want the sheet to update the list on the date that you look at it . I'm able to get everything to work if there was onlt 1 point to work with but once I throw in the 1/2 pint I cant get it to work correctly ?? HELP !!!

=IF((D2>G6),0,IF(D2>F6,0.5,1)) works with 1 point ...
How can I figure in if they were given a .5 point ??



<TABLE style="WIDTH: 626pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=833 border=0 x:str><COLGROUP><COL style="WIDTH: 151pt; mso-width-source: userset; mso-width-alt: 7350" width=201><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><TBODY><TR style="HEIGHT: 18pt" height=24><TD class=xl52 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 626pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 18pt; BACKGROUND-COLOR: white" width=833 colSpan=7 height=24>NEW ATTENDANCE POLICY TRACKER 3/3/08</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl42 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16 x:str="Todays Date ">Todays Date </TD><TD class=xl50 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white"></TD><TD class=xl37 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" x:str="'-------------------------->">--------------------------></TD><TD class=xl43 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" x:num="39881" x:fmla="=TODAY()">3/9/2009</TD><TD class=xl37 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white"></TD><TD class=xl37 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white"></TD><TD class=xl37 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white"></TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl36 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" height=19></TD><TD class=xl37 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white"></TD><TD class=xl38 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" x:err="#VALUE!">#VALUE!</TD><TD class=xl37 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white"></TD><TD class=xl37 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white"></TD><TD class=xl37 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white"></TD><TD class=xl37 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl47 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Date of Occurrence</TD><TD class=xl39 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Point Given</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Point Status</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Reason</TD><TD class=xl41 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=90>Action Taken</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Half point falls off</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Point falls off</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl48 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18 x:num="39859">Sunday, February 15, 2009</TD><TD class=xl51 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1.0</TD><TD class=xl44 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num x:fmla="=IF((D2>G6),0,IF(G6=F6,0.5, IF(D2<G6,0.5,1)))">0.5</TD><TD class=xl45 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl46 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl46 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="39919" x:fmla="=SUM(A5+60)">4/16/2009</TD><TD class=xl46 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="39979" x:fmla="=IF(B5=1,SUM(A5+120),SUM(A5+60))">6/15/2009</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl48 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="39861">Tuesday, February 17, 2009</TD><TD class=xl51 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1.0</TD><TD class=xl44 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num x:fmla="=IF((D3>G6),0,IF(G6=F6,0.5, IF(D2<G6,0.5,1)))">0.5</TD><TD class=xl45 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl46 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl49 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40039" x:fmla="=IF(A6<G5,G5+60,A6+60)">08/14/09</TD><TD class=xl46 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40099" x:fmla="=IF(B6=1,SUM(F6+60),SUM(A6+60))">10/13/2009</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl48 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="39862">Wednesday, February 18, 2009</TD><TD class=xl51 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>0.5</TD><TD class=xl44 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num x:fmla="=IF((D4>G7),0,IF(D4>F7,0.5,1))">0</TD><TD class=xl45 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl46 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl49 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40159" x:fmla="=IF(A7<G6,G6+60,A7+30)">12/12/09</TD><TD class=xl46 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40159" x:fmla="=IF(B7=1,SUM(F7+120),F7)">12/12/2009</TD></TR></TBODY></TABLE>

 

[nbrcrunch]

without the column and row references, it is impossible to translate the formula in to english. What do the cell references refer to?

 

[shemayisroel]

Hi & Welcome,

Would it be possible to post your sample data by using excel jeanie that way we can determine how it's all interated (if at all).

http://www.excel-jeanie-html.de/index.php?f=1

 

[shemayisroel]

without the column and row references, it is impossible to translate the formula in to english. What do the cell references refer to?

Had the same problem hence my post requesting excel jeanie

 

[mikerickson]

If the Date of Occurance is in col A , and the Point Given in col B
putting
=B2-INT((TODAY()-A2)/60)/2
in C2 and dragging down should do it.