If than else ?? or something else ?

joesanchez

New Member
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>

Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
without the column and row references, it is impossible to translate the formula in to english. What do the cell references refer to?

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

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.

Replies
7
Views
620
Replies
0
Views
596
Replies
0
Views
381
Replies
1
Views
235
Replies
2
Views
848

1,207,436
Messages
6,078,549
Members
446,347
Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back