Related:
- Excel if cell contains date then return value
- Excel if cell contains date - Best answers
- Excel if date - Best answers
- If cell = date return yes or no. ✓ - Forum - Office Software
- Excel if cell contains text then return value in another cell ✓ - Forum - Excel
- Excel if range of cell contain a text put a cell value in ✓ - Forum - Excel
- If cell A1 has text then cell B2 has today's Date ✓ - Forum - Excel
- Excel "IF" function w/ date in test cell ✓ - Forum - Excel
2 replies
BrianGreen
- Posts
- 1004
- Registration date
- Saturday January 17, 2015
- Status
- Moderator
- Last seen
- November 27, 2020
Would changing the enquiry help here ... instead of asking for a specific date then how about asking for ANY date? The expression then becomes...
=IF(ISNUMBER(A8),"1","0")You then need to format cell A8 as a date.
Report
JerrySD
- Posts
- 2
- Registration date
- Thursday November 12, 2015
- Status
- Member
- Last seen
- November 12, 2015
Thank you for the quick reply. I am so sorry I was unclear when I mentioned "company holidays". Since these are specific dates, "ANY" date will not work.
RayH
- Posts
- 122
- Registration date
- Tuesday August 31, 2010
- Status
- Contributor
- Last seen
- June 20, 2016
Brian is correct that you really shouldn't need to be entering hard-coded dates into formulas.
To achieve what you need.
1. Create a new sheet (called "CompanyDates") containing the dates that are deemed to be 'company holidays'
2 Enter this formula:
This will check the date in cell B2 and return a '--' if the date is not in the company holiday list or 1 if it is.
Even if more dates are added to the list (next year) it will still work.
To achieve what you need.
1. Create a new sheet (called "CompanyDates") containing the dates that are deemed to be 'company holidays'
2 Enter this formula:
=IF(ISNA(MATCH(B2,CompanyDates!A:A,0)),"--",1)
This will check the date in cell B2 and return a '--' if the date is not in the company holiday list or 1 if it is.
Even if more dates are added to the list (next year) it will still work.
On the same themes
- Highlight cell if another cell contains any text
- Excel if range of cell contain a text put a cell value in
- If a cell contains any text then add a value (with dates)
- Formula for if cell contains word, assign value for multiple
- change cell by clicking on another cell
- If cell A1 has text then cell B2 has today's Date
