Manual Formula to Arrayformula Google Sheets

1
votes

I have been dragging these formulas to end of the Google sheets which makes the sheet to much Heavier and that disturb to much while working to load/perform the action.

I am trying to convert these formulas into Array.

Any help will be highly appreciated.

Sheet Link https://docs.google.com/spreadsheets/d/1pgVGJxkw39uelgPU9ceZLXflnP8KaFGou4rJfSsfVXc/edit#gid=0

=IF(AND(E3<>"",B3<>""),EDATE(E3,6),"")

=IF(F3="Confirmed","13",IF(F3="Probation","0",IF(F3="Terminate","",IF(F3="",""))))

=IF(F2="Confirmed",IFERROR(IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=1,12,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=2,11,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=3,10,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=4,9,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=5,8,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=6,7,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=7,6,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=8,5,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=9,4,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=10,3,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=11,2,IF(MONTH(DATEVALUE(TEXT(G2,"mmm")&" 1"))=12,1))))))))))))*13/12),IF(F2="Probation","0",""))

=IF(F2<>"",K2+IF(I2=0,H2-L2,I2-L2),"")
2
google-sheets

2 Answers

1
votes

The following four formulas should work for you.

Formula #1

=ArrayFormula(IF((E3:E<>"")*(B3:B<>""),EDATE(E3:E,6),""))

Formula #2

=ArrayFormula(IFS(F3:F="Confirmed","13",F3:F="Probation",0,F3:F="Terminate","",F3:F="",""))

Formula #3

=ArrayFormula(IF(F3:F="Confirmed",
  IFERROR(IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=1,12,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=2,11,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=3,10,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=4,9,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=5,8,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=6,7,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=7,6,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=8,5,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=9,4,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=10,3,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=11,2,
          IF(MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))=12,1))))))))))))*13/12),
  IF(F3:F="Probation",0,"")))

Formula #4

=ArrayFormula(IF(F3:F<>"",O3:O+IF(K3:K=0,I3:I-P3:P,K3:K-P3:P),""))

UPDATE: Try the following formula to calculate the months of allocated leave, replacing the third formula above:

=ArrayFormula(IF(LEN(G3:G),
                (13 - MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))) * 13/12,
                ""))

UPDATE#2 Corrected the above formula to add back two criteria checks, which I'd omitted.

=ArrayFormula(IF(LEN(G4:G),
   IF(F4:F="Confirmed",
     (13 - MONTH(DATEVALUE(TEXT(G4:G,"mmm")&" 1"))) * 13/12,
     IF(F4:F="Probation",0,0)),""))

Note that the last IF test could be eliminated, and replaced with simply "0", but perhaps you want it there for future reference. I also changed the "0" to just a numeric 0, in all of the formulas.

1
votes

Leaves During Probation excluding "C" values:

=ArrayFormula(IFERROR(1/(1/(IF(B3:B="",,LEN(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(
 IFERROR(VLOOKUP(ROW(Jan!A3:A), {ROW(Jan!A3:A), Jan!C3:AG, Feb!C3:AE, Mar!C3:AG, Apr!C3:AF, May!C3:AG, Jun!C3:AF, Jul!C3:AG, Aug!C3:AG, Sep!C3:AF, Oct!C3:AG, Nov!C3:AF, Dec!C3:AG}, 
 IF(1=SPLIT(REPT("01", DATE(YEAR(E3:E), MONTH(E3:E)+6, DAY(E3:E))-("01/01/2020"-1)), 0), COLUMN(B:AAA), )*1, 0))<>"C", 
 IFERROR(VLOOKUP(ROW(Jan!A3:A), {ROW(Jan!A3:A), Jan!C3:AG, Feb!C3:AE, Mar!C3:AG, Apr!C3:AF, May!C3:AG, Jun!C3:AF, Jul!C3:AG, Aug!C3:AG, Sep!C3:AF, Oct!C3:AG, Nov!C3:AF, Dec!C3:AG}, 
 IF(1=SPLIT(REPT("01", DATE(YEAR(E3:E), MONTH(E3:E)+6, DAY(E3:E))-("01/01/2020"-1)), 0), COLUMN(B:AAA), )*1, 0)), )),,9^9))), " ", )))))))

Compensated Leaves:

=ArrayFormula(IFERROR(1/(1/(IF(B3:B="",,LEN(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(
 {Jan!C3:AG, Feb!C3:AG, Mar!C3:AG, Apr!C3:AG, May!C3:AG, Jun!C3:AG, Jul!C3:AG, Aug!C3:AG, Sep!C3:AG, Oct!C3:AG, Nov!C3:AG, Dec!C3:AG}="C", 
 {Jan!C3:AG, Feb!C3:AG, Mar!C3:AG, Apr!C3:AG, May!C3:AG, Jun!C3:AG, Jul!C3:AG, Aug!C3:AG, Sep!C3:AG, Oct!C3:AG, Nov!C3:AG, Dec!C3:AG}, )),,9^9))), " ", )))))))

Total Leaves taken This Year no matter the probation

=ArrayFormula(IFERROR(1/(1/(IF(B3:B="",,LEN(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE( 
 {Jan!C3:AG, Feb!C3:AG, Mar!C3:AG, Apr!C3:AG, May!C3:AG, Jun!C3:AG, Jul!C3:AG, Aug!C3:AG, Sep!C3:AG, Oct!C3:AG, Nov!C3:AG, Dec!C3:AG}),,9^9))), " ", )))))))