# Updating a formula in excel

#### dpaton05

##### Well-known Member
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?

### 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
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
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
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"})))``

Thanks Dante.

#### DanteAmor

##### Well-known Member
Youre welcome, thanks for the feedback.