Tricky Formula

[inkserious]

I, along with some help, put this formula together some time ago to calculate employee attendance. We use a point system, and after 90 consecutive days of perfect attendance, we remove one point. I had to add the INDEX function to allow for some special circumstances: we do not give points for Jury Duty "JUR", Bereavement "BER" or FMLA "FMLA"; however, we still log them for tracking purposes. Originally, I had the formula set to ignore any absences that fell under these types. However, a problem has surfaced; although we do not assess points for an employee when they are absent due to FMLA, we are not going to count that day toward the 90 day perfect attendance window. Hence, if they had an absence due to FMLA, they would have to go 91 days since their previous absence. I'm struggling to find a way to tell the formula to add a day to the 90 day window for each FMLA day it finds.

I know this is long winded, and I hope it makes sense. Any help would be greatly appreciated. -ep

Column D contains the date of the absence
Column F contains the absence type: Absent, Late, FMLA, etc.
Column G contains the number of points assessed.
Column H contains the following formula used to calculate the roll-off.

{=IF(OR($D12=0,$G12<=0),"",IF($D13-$D12>90,-1,IF(AND($D13="",$G12>0,TODAY()-$D12>90),-1,-(INDEX($D13:$D$100,MIN(IF(($D13:$D$100<=TODAY())*($F13:$F$100<>"BER")*($F13:$F$100<>"JUR")*($F13:$F$100<>"FMLA"),ROW($F13:$F$100)-ROW(D12),"")))-$D12>90))))}

 

[SkipVought]

Hi,

1. it would help to have a cogent sample table posted, and the expected result, that you are obviously not getting. Please use TGML tags type TT. Check the link below to determine how to do that. Replace each TAB when you copy/paste cells with SPACES to line everything up in the proper column.

2. You also need to state the entire requirement for this calculation.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I do not understand...
[tt]
,-(INDEX($D13:$D$100
,MIN(IF(($D13:$D$100<=TODAY())*($F13:$F$100<>"BER")*($F13:$F$100<>"JUR")*($F13:$F$100<>"FMLA")
,ROW($F13:$F$100)-ROW(D12)
,""
)
)
)-[red]$D12>90[/red]
)
[/tt]
condensed would be...
[tt]
(The value that INDEX returns)-[red]$D12>90[/red])
[/tt]
BUT [red]$D12>90[/red] is a boolean expression (TRUE/FALSE)




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[inkserious]

Thanks Skip. Here is the formula using TGML. In it's current state, it will return a value of negative one if the first date in column D is, for example, 10/28/2010 and the second date is 01/27/2011. There are 91 days between the two dates, so it returns -1 which represents the 90 day roll-off.

I need to be able to insert an FMLA entry between the two dates, and have the 90-day roll-off increase to 91 days. For each FMLA entry, the 90-day roll off window would increase by one. Thanks again. I hope this explains things better. -ep

{=IF(OR($D12=0,$G12<=0),"",IF($D13-$D12>90,-1,IF(AND($D13="",$G12>0,TODAY()-$D12>90),-1,-(INDEX($D13:$D$100,MIN(IF(($D13:$D$100<=TODAY())*($F13:$F$100<>"BER")*($F13:$F$100<>"JUR")*($F13:$F$100<>"FMLA"),ROW($F13:$F$100)-ROW(D12),"")))-$D12>90))))}

[tt]Example 1 working as expected. The value of -1 is returned in column H since the dates in column D are greater than 90 days[/tt]
[tt]D E F G H[/tt]
[tt]Date Day Absence Type Points Roll-off[/tt]
[tt]10/28/10 Thursday A 1.0 -1.0[/tt]
[tt]01/27/11 Thursday A 1.0[/tt]

[tt]Example 2 not working correctly. Column H should not return a value at all. Although the difference between the two absence dates is greater than 90 days, there is an FMLA day in between them. So the roll-off window should now be 91 days. The desired result would be for column H to return nothing[/tt]
[tt]D E F G H[/tt]
[tt]Date Day Absence Type Points Roll-off[/tt]
[tt]10/28/10 Thursday A 1.0 -1.0[/tt]
[tt]01/01/11 Saturday FMLA 0.0[/tt]
[tt]01/27/11 Thursday A 1.0[/tt]

[tt]Example 3 is the desired result. Column H should return a value of -1. There are 92 days between the first absence and the second absence which accounts for the FMLA entry [/tt]
[tt]D E F G H[/tt]
[tt]Date Day Absence Type Points Roll-off[/tt]
[tt]10/28/10 Thursday A 1.0 -1.0[/tt]
[tt]01/01/11 Saturday FMLA 0.0[/tt]
[tt]01/28/11 Friday A 1.0[/tt]

 

[SkipVought]

...and the answer to my last post?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Cotton9]

As a 'very' quick stab at it try.
Replaceing the ending "-$D12>90" with

-$D12>(90 + CountIf($F13:$F$100, "=FMLA"))

I'm still learning VBA by doing so test -- test -- test.



D. Buckman
US Army Corps of Engineers, Omaha

Learn from the past, Live in the present, Create the future

 

[SkipVought]

The issue is that BOTH [red]$D12>90[/red] AND [red]$D12>(90 + CountIf($F13:$F$100, "=FMLA"))[/red] are boolean expressions that evaluate to TRUE or FALSE and not a numeric value!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[inkserious]

Skip,

To be honest, I get lost in the INDEX portion of the formula. It does, however, work with the exception of the issue I am trying to solve now. I would be happy to post an example of the workbook.

Thanks again.

-ep

 

[SkipVought]

As I stated earlier, this does NOT get into the operation of the INDEX formula.

It takes (The value that INDEX returns)-[red]$D12>90[/red].

Which could evaluate to
[tt]
-1-[red]TRUE[/red]
[/tt]
or
[tt]
-1-[red]FALSE[/red]
[/tt]
Does that make sense?




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Cotton9]

Set up each of you examples along with the formula and add the suggested change:

Replacing the ending "-$D12>90" with

-$D12>(90 + CountIf($F13:$F$100, "=FMLA"))

It seems to work. If you have 'Show a zero in cells that have a zero value' disabled it will show a blank cell instead of a zero for example #3.

Agree with Skip, showing a '-1' or blank doesn't seem to be good logic but if it works for you, go for it.


D. Buckman
US Army Corps of Engineers, Omaha

Learn from the past, Live in the present, Create the future

 

[Gruuuu]

Cotton9, it may return a result, which may be expected, but what Skip's trying find out is what is the purpose of that expression:
[tt]
-D12>(90 + CountIf($F13:$F$100, "=FMLA"))[/tt]
we KNOW what the RESULTS of the expression are (either 0, or 1) but that's being subtracted from whatever results the INDEX returns. Which just seems odd.

 

[inkserious]

Is there a better way to write the formula to return the expected results instead of relying on the boolean expression to return the results? It seems that in the formula's current form, removing

*(F13:F100<>"FMLA")

returns the expected results under all conditions. However, I need to find a way to add FMLA so that it adds each FMLA occurrence to the 90 day window. In other words, two FMLA occurrences would increase the 90 day window to 92 days. Once 92 days is reached, 1 point would roll off and the 90 day window would start over. Using

-D12>(90 + COUNTIF($F13:$F$100, "=FMLA"))

counts down the entire column. I only need it to count until it reaches a 90 day difference between the first absence date and subsequent absence date PLUS any FMLA occurrences within these dates.

Thanks again.

 

[Cotton9]

Give this a try.

=IF(OR($D12=0,$G12<=0),"",IF($D13-$D12>90,-1,IF(AND($D13="",$G12>0,TODAY()-$D12>90),-1,-(INDEX( $D13:$D$100,MAX(IF(($D13:$D$100<=TODAY())*($F13:$F$100 <>"BER")*($F13:$F$100<>"JUR")*($F13:$F$100<>"FMLAa")*($F13:$F$100<>""),ROW($F13:$F$100)-ROW( D12),"")))-$D12>(90+COUNTIF(INDIRECT("$F$13:$F$"&ROW(INDEX($F13:$F$100,MAX(IF(($D13:$D$100<=TODAY())*( $F13:$F$100<>"BER")*($F13:$F$100<>"JUR")*($F13:$F$100 <>"FMLAa")*($F13:$F$100<>""),ROW( $F13:$F$100)-ROW(D12),"")))),TRUE),"=FMLA"))))))

Changed the 'MIN' function to 'MAX' and added:

* ( $F13:$F$100 <> "")

after the first "FMLA" test. This will key on the last date entered in the Date column.

Disabled the test for “FMLA” in the two MAX() functions by adding and ‘a’ ("FMLAa") character so it will always return false and you can see what was done or can ‘undo’ if needed.

Replaced the CountIF function suggested earlier with:

COUNTIF(INDIRECT("$F$13:$F$"&ROW(INDEX($F13:$F$100,MAX(IF(($D13:$D$100<=TODAY())*($F13:$F$100<>"BER")*($F13:$F$100 <> "JUR")*($F13:$F$100 <>""),ROW($F13:$F$100)-ROW(D12),"")))),TRUE),"=FMLA")

This ‘seems’ to work on a simple table.




D. Buckman
US Army Corps of Engineers, Omaha

Learn from the past, Live in the present, Create the future