I will show you how to calculate a 24 hours downtime (outage) for different countries by taking in account its bank holidays and its time schedule (in my example, a shop schedule).

 

When I use the formula ?

When I need to know the exact time of an outage for each country.

 

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

 

How is/are the formula(s) ?

=TEXT()

=IF()

=VLOOKUP()

=INT()

=ROUND()

=COUNTIFS()

=COUNTIF()

=MEDIAN()

Before to begin in the “Sheet1”, I need to create in “Sheet2” those columns:

• Column A: a calendar of the year
• Column B: the day corresponding to the date with this formula in the cell B2:
  =TEXT(A2, "DDDD")
• Column C: show the column F corresponding to the day with this formula in the cell C2:
  =IF(VLOOKUP($B2,$E$2:$E$8,1,0)="Monday",IF($F$2=0,"",$F$2),IF(VLOOKUP($B2,$E$2:$E$8,1,0)="Tuesday",
  IF($F$3=0,"",$F$3),IF(VLOOKUP($B2,$E$2:$E$8,1,0)="Wednesday",IF($F$4=0,"",$F$4),IF(VLOOKUP($B2,
  $E$2:$E$8,1,0)="Thursday",IF($F$5=0,"",$F$5),IF(VLOOKUP($B2,$E$2:$E$8,1,0)="Friday",IF($F$6=0,"",
  $F$6),IF(VLOOKUP($B2,$E$2:$E$8,1,0)="Saturday",IF($F$7=0,"",$F$7),IF(VLOOKUP($B2,$E$2:$E$8,1,0)=
  "Sunday",IF($F$8=0,"",$F$8),"")))))))
• Column E: the day of the week
• Column F: the closure country of the shop
• Column H to K: bank holidays of the country

Next, for each bank holidays of the country (column H to K), I will create an individual identification by clicking on “formulas -> name manager”:

Click on “new”:

Fill up the 2 fields:

• Name: put a name
• Refers to: put the corresponding range

Back to the “Sheet1”, don’t forget that the calculation is done only for 1 full day (24 hours):

• Column A: country name
• Column B: time difference in CET taking as main reference “Spain”
• Column C: opening time of the shop with this formula in the cell C3:
  =E3+10/24
  NOTE: if the shop opens at 8h, change 10 by 8
• Column D: closing time of the shop with this formula in the cell D3:
  =C3+10/24
  NOTE: if the shop opens at 8h and it closes at 23h59, change 10 by 15.9998 (i.e. China)
• Column E to G:
  • The “orange” cells in E2, F2 and G2: where I put the outage information
  • Column E: put this formula in the cell E3:
    =INT(F3)
  • Column F: put this formula in the cell F3:
    =$E$2+($F$2+B3/24)
  • Column G: put this formula in the cell G3:
    =F3+(($G$2-$F$2+($G$2<$F$2))*24)/24

• Column H: put this formula in the cell H3:
  =IF(COUNTIFS(Sheet2!$A:$A,$E3,Sheet2!$C:$C,"*"&$A3&"*")>0,0,IF(COUNTIF(HolidayES,E3),0,IF(F3=MEDIAN
  (C3,D3,F3),IF(G3=MEDIAN(C3,D3,G3),G3-F3,IF(D3-F3<0,0,D3-F3)),IF(G3=MEDIAN(C3,D3,G3),G3-C3,IF(C3=
  MEDIAN(F3,G3,C3),D3-C3,0))))+IF(COUNTIF(HolidayES,E3+1),0,IF(INT(G3)>INT(F3),IF($E$2+($G$2+B3/24)=
  MEDIAN(C3,D3,$E$2+($G$2+B3/24)),($E$2+($G$2+B3/24))-C3,0),0)))
• Column I: put this formula in the cell I3:
  =ROUND(H3*1440,0)

NOTE:

• For Spain, the "outage country" is only 1 hour because the day 29/03/2024 is a bank holiday based on the “Sheet2” and the outage ends the next day at 11h:
  
• For China, the “outage country” is 0 because the day 30/03/2024 is a closing day based on the “Sheet2”: