Excel: If Date =, then enter a value [Solved/Closed]

JerrySD
Posts
2
Registration date
Thursday November 12, 2015
Last seen
November 12, 2015
- Nov 12, 2015 at 04:53 PM - Latest reply: BrianGreen
Posts
983
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 12, 2018
- Nov 13, 2015 at 12:42 PM
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)?
See more 

4 replies

Best answer
BrianGreen
Posts
983
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 12, 2018
- Nov 12, 2015 at 05:44 PM
2
Thank you
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.

Thank you, BrianGreen 2

Something to say? Add comment

CCM has helped 1621 users this month

JerrySD
Posts
2
Registration date
Thursday November 12, 2015
Last seen
November 12, 2015
- Nov 12, 2015 at 06:10 PM
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
- Nov 12, 2015 at 07:37 PM
1
Thank you
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.
BrianGreen
Posts
983
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 12, 2018
- Nov 13, 2015 at 12:42 PM
Thanks Ray :)