Google Sheet Formula is much heavier to load

Question

Using below formula to count the leaves between the dates. That formula sometimes give result and sometime sheet keep gives an error of reloading the sheet.

I think this formula is much heavier to make calculation.

I have attached an sheet where formula works.

looking for an easier way or formula to make calculation that below formula does. Your help will be appreciated.

=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))), " ", )))))))

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

error.

can you add the expected output? your formula doesn't load for me. — Umar.H
Sir removed the formula from sheet now sheet is working and expected result is mentioned besides the formula column. — Strenuous
probation over = leave ? how do you define a range, is it calculated by the next available date? in the array? — Umar.H
I have pasted a formula which i was using before to calculate the leaves please look into it you will understand what i am trying to do — Strenuous
Yes it is calculated by by both dates For example Joining >= date and Probation over <= date — Strenuous

NaziA NaziA · Accepted Answer · 2020-12-29T20:14:57

Upon checking, it seems there is nothing wrong with your formula. And I see even worse formula than this that are still functioning correctly on large data sets so I did check the data itself.

Problem:

The days in the month sheets you are comparing to your Sheet1 columns E and G are just integers/numbers resulting COUNTIFS calls having false conditions.

Fix:

Convert the integer/number days in the first rows of all sheets to date format.

I just converted all day cells in all sheets into dates and show them as just day in custom date format (to show the same format of your sheets). By doing this, you make COUNTIFS function properly and return the proper number of leaves.

Showing only in cells as days but containing date values

Expected result is being matched

Although, you mistakenly counted for Employee5 as 1 instead of 4

Google Sheet Formula is much heavier to load

1 Answers