johnmheinzel
New Member
I am being asked to provide an expected delivery date to a customer for a list of orders. Our business system can produce the report with a delivery date but does not consider weekends or holidays. So I need to take the report provided by the system and either provide the existing date, or if that date is a weekend or holiday return the next business day instead.
If it were just weekends, with the date in A1, the formula could be =IF(WEEKDAY(A1)=1,A1+1,IF(WEEKDAY(A1)=7,A1+2,A1)).
I could use that formula to create a two column table with every date in the next 2 years in the first column and the next business day (or same day if not a weekend or holiday) in the second column, then do an XLOOKUP. But is there a more elegant solution? Considering business holidays are not exactly standard, I understand I will need a table with the holidays somewhere to refer to. But I'm looking to create a template where the user can drop the report data and have a calculated column that shows the actual delivery date in replacement of the system calculated date. Thank you in advance for any helpful suggestions.
If it were just weekends, with the date in A1, the formula could be =IF(WEEKDAY(A1)=1,A1+1,IF(WEEKDAY(A1)=7,A1+2,A1)).
I could use that formula to create a two column table with every date in the next 2 years in the first column and the next business day (or same day if not a weekend or holiday) in the second column, then do an XLOOKUP. But is there a more elegant solution? Considering business holidays are not exactly standard, I understand I will need a table with the holidays somewhere to refer to. But I'm looking to create a template where the user can drop the report data and have a calculated column that shows the actual delivery date in replacement of the system calculated date. Thank you in advance for any helpful suggestions.
