Previous Topic Next Topic

Excel: If Date =, then enter a value

Solved/Closed
Report
Posts
2
Registration date
Thursday November 12, 2015
Status
Member
Last seen
November 12, 2015
 -
Posts
1010
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
September 30, 2021
 -
I am using this formula and it works well: =IF(A8=DATE(2015,3,27),"1","--")

How do I now create a string to check a number of dates (company holidays)?
Related:

2 replies

Answer 1 / 2
Posts
1010
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
September 30, 2021
 146
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.
2
Report
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.
0
Answer 2 / 2
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
 24
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: 
=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.
1
Report
Posts
1010
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
September 30, 2021
 146
Thanks Ray :)
0
Ask a question