Index, Match, NetworkDays, Sum?

[pterribilis]

Hi All,

I am struggling at this stage and have been resisting having this created in a database but sometimes it is hard to convince your employer to move away from spreadsheets. With that said, I would be most appreciative if someone could help me figure this out:

=((F$1-IFERROR(MAX(0,NETWORKDAYS(MAX(F$2,INDEX(leave_from,MATCH($B3,leave_person,0))),MIN(DATE(YEAR(F$2),MONTH(F$2)+1,0),INDEX(leave_to,MATCH($B3,leave_person,0))))),0))*$E3)/F$1


That is currently my formula which calculates the FTE of a person considering the number of working days in a month, the number of days they have been approved for leave, and multiplies that by their FTE percentage. This formula is entered in each corresponding cell below the date (heading) Some context:

leave_from = From Date
leave_to = To Date
leave_person = The person taking leave being looked up​

The issue I have is, what if the person has multiple leave entries? How can I modify the formula to sum up the number of days across all of the entries?

[TABLE="width: 382"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Person[/TD]
[TD]FromDate[/TD]
[TD]ToDate[/TD]
[/TR]
[TR]
[TD]Person A[/TD]
[TD="align: right"]9/02/2018[/TD]
[TD="align: right"]22/02/2018[/TD]
[/TR]
[TR]
[TD]Person A[/TD]
[TD="align: right"]27/12/2017[/TD]
[TD="align: right"]14/01/2018[/TD]
[/TR]
</tbody>[/TABLE]


at the moment for the month of February it takes away 10 from the 20 working days in February, but it ignores the days off between December of January.

Anyone? please?

 

[Chrisdontm]

Re: Extreme Formula - Index, Match, NetworkDays, Sum? Array Formula Help

What goes in Cells: F1, F2, & E3 ?
I assume that there is a drop down in B3 of the persons in the list of "Leave_person"
And,
Are you saying that there is a column named: "Heading" where the formula goes...?
or is there one cell where the answers go depending on who you choose in cell B3...?

When you answer these questions perhaps someone can zero in on your issue...

 

[barry houdini]

Re: Extreme Formula - Index, Match, NetworkDays, Sum? Array Formula Help

Try this "array formula" [edited]
<f$2,f$2,leave_from),if(leave_to><f$2,f$2,leave_from),if(leave_to>
=(1-SUM(IF(leave_person=$B3,TEXT(NETWORKDAYS(IF(leave_from< F$2,F$2,leave_from),IF(leave_to >EOMONTH(F$2,0),EOMONTH(F$2,0),leave_to)),"0;\0")+0))/F$1)*$E3

confirm with CTRL+SHIFT+ENTER</f$2,f$2,leave_from),if(leave_to></f$2,f$2,leave_from),if(leave_to>