Updating a formula in excel

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,216
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?
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,692
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,216
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
10,271
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
10,271
Office Version
2007
Platform
Windows
Youre welcome, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,424
Messages
5,414,383
Members
403,526
Latest member
swedeness50

This Week's Hot Topics

Top