Forum Discussion

Tracie29's avatar
Tracie29
Copper Contributor
Oct 28, 2022

Recognise holidays

I have a spreadsheet with several sheets.

Amongst these are sheets for the banks bills come out of which look at whether a day is the day a bill is due and return the amount due.

I can get it to recognise whether it is a weekday and allow for weekends, I am struggling to get it to recognise holidays.

 

Natwest_Weekday=OR(Natwest[@Day]="Monday",Natwest[@Day]="Tuesday",Natwest[@Day]="Wednesday",Natwest[@Day]="Thursday",Natwest[@Day]="Friday")

Nest_Amount ='Due Days and Amounts'!$B$7

Nest_Day='Due Days and Amounts'!$C$7

Nest_Weekday =AND((DAY(Natwest[@Date])=Nest_Day),Natwest_Weekday)

Nest_Weekend=AND((Natwest[@Day])="Monday",OR(DAY(Natwest[@Date])=Nest_Day+1,DAY(Natwest[@Date])=Nest_Day+2))

Nest =IF(OR(Nest_Weekday,Nest_Weekend),Nest_Amount,0)

 

excel
Formulas and Functions

2 Replies

  • Nothing_Left_to_Lose's avatar
    Nothing_Left_to_Lose
    Brass Contributor
    Oct 28, 2022
    Have you looked at the NetWorkDays function?
    =NETWORKDAYS(start_date, end_date, [holidays])

    '---
    Nothing Left To Lose
    https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU
    (free excel programs)
    • Tracie29's avatar
      Tracie29
      Copper Contributor
      Oct 29, 2022

      Nothing_Left_to_Lose I don't think that would work. My dates down side cover several months. They are currently running from now until end March 2023. (Due to tax year). The bills come out on the same day each month. Normally anything due on a weekend is paid on a Monday. If a payment becomes due on a bank holiday, it gets taken the next working day which can be upto 4 later e.g. the Tuesday after Good Friday. I don't think NETWORKDAYS allows for repeated occurrences on same day of each month or for variations in month lengths.