Menu
Ask a question Report

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

JerrySD 2 Posts Thursday November 12, 2015Registration date November 12, 2015 Last seen - Nov 12, 2015 at 04:53 PM - Latest reply: BrianGreen 973 Posts Saturday January 17, 2015Registration dateModeratorStatus August 6, 2018 Last seen
- Nov 13, 2015 at 12:42 PM
Excel Ask a question Report
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
Report Respond to BrianGreen
BrianGreen 973 Posts Saturday January 17, 2015Registration dateModeratorStatus August 6, 2018 Last seen - 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 1902 users this month

Report
JerrySD 2 Posts Thursday November 12, 2015Registration date November 12, 2015 Last seen - 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.
Report Respond to RayH
RayH 122 Posts Tuesday August 31, 2010Registration dateContributorStatus June 20, 2016 Last seen - 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.
Report
BrianGreen 973 Posts Saturday January 17, 2015Registration dateModeratorStatus August 6, 2018 Last seen - Nov 13, 2015 at 12:42 PM
Thanks Ray :)
Ask a question
Featured