0

I am trying to calculate the elapsed time between a specific date and time and another date and time, excluding weekends and non-office hours. I have done everything already and all seems to work correctly but for one formula. Here you can see the formula that gives me problems, while here you can see the same formula without the ArrayFormula encapsulation. The result should be 08:00:00 like in screenshot 2, i can't understand what's going on with the first screenshot.

I need to have the formulas applied when the value are written in the columns from D to G, not before, that's the reason why i am using ArrayFormula. If there is another way to achieve the same result in another way let me know.

Thanks.

Edit: i have found that the issue is that the formula in column K is referencing a fixed cell in column E in the last bracket, E2:E is always a fixed cell in column E (see this screenshot). Here you can download a copy, thanks in advance!

Laurent Capello
  • 1
  • 1
  • 1
    It would help if you could share your spreadsheet file for replication purposes. – SputnikDrunk2 Jul 27 '21 at 16:14
  • this is unsolvable unless you share a copy of your sheet or at least what is in B column on sheet `Constants` – player0 Jul 27 '21 at 18:48
  • Although this may not be the most sophisticated solution/workaround, you can try the answer from this quite identical post at https://stackoverflow.com/a/29887754 (by rewriting the logic of MIN()). Your formula on Column K could be something like: =ARRAY_CONSTRAIN(ARRAYFORMULA(if(isblank(D2:D),,if(H2:H=0,0,ARRAYFORMULA(IF(Constants!$B$3 < Constants!$B$2-E2:E, TO_TEXT(Constants!$B$3), TO_TEXT(Constants!$B$2-E2:E)))))), 1027, 2) – SputnikDrunk2 Jul 28 '21 at 16:37

1 Answers1

0

MIN is not supported by ARRAYFORMULA

You should instead use if to do the comparison

=ArrayFormula(if(isblank(D2:D),,if(H2:H=0,0,if(Constants!$B$3<Constants!$B$2-E2:E,Constants!$B$3,Constants!$B$2-E2:E))))
idfurw
  • 5,727
  • 2
  • 5
  • 18