Need help in complex formula calculation for presence work presence across months

[inddon]

Hello There,

I have a requirement to calculate the presence of a person's availability based on working hours (FTE) on the start and an end date. The calculation is displayed across the months (Jan to Dec).

I have uploaded the sample file.

For ease of understanding, I have given range names to columns used in formulas. I tried with the first row Person Number=Donnie, found it very complex to go ahead.

The sample file displays the exact way it should look. Here the individual months values needs to be converted into formulas.

Very much appreciate your help in getting this done.

Look forward to hearing from you.

Regards,
Don

 

[bosco_yip]

1] Your "Expected Output" I put it to A29:Q42, and the "Formula output" is in A6:Q19

2] In "Formula Result" D8, copied across and down :

=IFERROR(1/(1/IF((MONTH($B9)=MONTH(E$4))*(DAY($B9)>1),$D9*DAY($B9)/E$8,$D9*MAX(0,MIN(E$5,IF($C9="",ReportToDate,$C9))-MAX(E$4,$B9)+1)/E$8)),"")

P.s.

The above formula logic is similar to your other post in :
https://chandoo.org/forum/threads/need-a-formula-to-generate-month-wise-date-based-on-a-date-range.40198/

Regards
Bosco

 

[inddon]

Thank you very much Bosco. It works as expected. I thought it would be a very complex one but you have made it look simple.

In another worksheet it should reflect the summary of the Person Number wise (unique) listing and it's FTE Months. I will try it out myself, if I can get it working. Will keep you posted.


Regards,
Don

 

[inddon]

1] Your "Expected Output" I put it to A29:Q42, and the "Formula output" is in A6:Q19

2] In "Formula Result" D8, copied across and down :

=IFERROR(1/(1/IF((MONTH($B9)=MONTH(E$4))*(DAY($B9)>1),$D9*DAY($B9)/E$8,$D9*MAX(0,MIN(E$5,IF($C9="",ReportToDate,$C9))-MAX(E$4,$B9)+1)/E$8)),"")

P.s.

The above formula logic is similar to your other post in :
https://chandoo.org/forum/threads/need-a-formula-to-generate-month-wise-date-based-on-a-date-range.40198/

Regards
Bosco

Hi Bosco,

As a summary of the detail listing, I tried to get the unique listing of the persons and it's totals of the relevant columns against each person.

Could you please have a look at the attached sample file and help me.

Many thanks and look forward to hearing from you.

Regards,
Don

 

[bosco_yip]

Hi Bosco,

As a summary of the detail listing, I tried to get the unique listing of the persons and it's totals of the relevant columns against each person.

Could you please have a look at the attached sample file and help me.

Many thanks and look forward to hearing from you.

Regards,
Don

Unique person schedule summary in A30:Q40 with helper column in S9:S19

1] In "Helper" S9, copied down :

=IF(A9=A10,D9&", "&S10,D9)

2] In Summary "Person number" A33, copied down :

=IFERROR(INDEX(A$9:A$19,MATCH(0,INDEX(COUNTIF(A$32:A32,A$9:A$19),0),0)),"")

3] In Summary "Start date" B33, copied down :

=IF(A33="","",INDEX(B$9:B$19,MATCH(A33,A$9:A$19,0)))

4] In Summary "End date" C33, copied down :

=IFERROR(1/(1/LOOKUP(2,1/(A$9:A$19=A33),C$9:C$19)),"")

5] In Summary "FTE" D33, copied down :

=IF(A33="","",INDEX(S$9:S$19,MATCH(A33,A$9:A$19,0)))

6] In schedule summary breakdown E33, copied across and down :

=IFERROR(1/(1/SUMIF($A$9:$A$19,$A33,E$9:E$19)),"")

Regards
Bosco

 

[inddon]

Unique person schedule summary in A30:Q40 with helper column in S9:S19

1] In "Helper" S9, copied down :

=IF(A9=A10,D9&", "&S10,D9)

2] In Summary "Person number" A33, copied down :

=IFERROR(INDEX(A$9:A$19,MATCH(0,INDEX(COUNTIF(A$32:A32,A$9:A$19),0),0)),"")

3] In Summary "Start date" B33, copied down :

=IF(A33="","",INDEX(B$9:B$19,MATCH(A33,A$9:A$19,0)))

4] In Summary "End date" C33, copied down :

=IFERROR(1/(1/LOOKUP(2,1/(A$9:A$19=A33),C$9:C$19)),"")

5] In Summary "FTE" D33, copied down :

=IF(A33="","",INDEX(S$9:S$19,MATCH(A33,A$9:A$19,0)))

6] In schedule summary breakdown E33, copied across and down :

=IFERROR(1/(1/SUMIF($A$9:$A$19,$A33,E$9:E$19)),"")

Regards
Bosco

Thanks a lot Bosco for taking the time to help me out.

Wish you a pleasant weekend.

Regards,
Don

 

[inddon]

Unique person schedule summary in A30:Q40 with helper column in S9:S19

1] In "Helper" S9, copied down :

=IF(A9=A10,D9&", "&S10,D9)

2] In Summary "Person number" A33, copied down :

Regards
Bosco

Hi Bosco,

There is a slight problem with the calculation of the formula in the detail section 1st part.

I have attached the sample file for your reference. The 2 rows are marked in Bold. The one in green first row is working good. The second row formula marked in red is giving problem. I have given the explanation of the same in the file.

I tried to modify the formula but couldn't, therefore seek your help.

Could you please have a look.

Look forward to hearing from you. Thanks again for your help

Regards,
Don