Updating a formula in excel

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,130
I have a formula that correctly returns if a date is a mon-fri, weekend or public holiday.

Code:
=IF(A11="","",IF(COUNTIF(Sheet2!$G$87:$DO$97,A11),"Public Holiday",IF(WEEKDAY(A11,2)>5,"Weekend","Mon-Fri")))
I need it to now determine if the day is a mon-fri, saturday, sunday or public holiday.

Can someone help me with the excel formula to achieve this please?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,691
Holidays is a named range that holds the dates of all public holidays for the current year or the year pertinent to the range of dates that might be entered in cell A11.
Rich (BB code):
=IF(A11="","",IF(ISNUMBER(MATCH(A11,Holidays,0)),"Public Holiday",CHOOSE(WEEKDAY(A11),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")))
 
Last edited:

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,130
Thanks Joe but I already worked it out with this formula

Code:
=IF(A11="","",IF(COUNTIF(Sheet2!$G$87:$DO$97,A11),"Public Holiday",IF(WEEKDAY(A11)=1,"Sunday",IF(WEEKDAY(A11)=7,"Saturday","Mon-Fri"))))
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,710
Office Version
2007
Platform
Windows
A little shorter

Code:
=IF(A11="","",IF(COUNTIF(Sheet2!$G$87:$DO$97,A11),"Public Holiday",LOOKUP(WEEKDAY(A11,2),{1,6,7},{"Mon-Fri","Saturday","Sunday"})))
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,710
Office Version
2007
Platform
Windows
Youre welcome, thanks for the feedback.
 

Forum statistics

Threads
1,085,834
Messages
5,386,227
Members
401,986
Latest member
crt54

Some videos you may like

This Week's Hot Topics

Top