Excel: If Date =, then enter a value

Solved/Closed

JerrySD

Posts: 2
Registration date: Thursday November 12, 2015
Status: Member
Last seen: November 12, 2015

- Nov 12, 2015 at 04:53 PM
BrianGreen

Posts: 1010
Registration date: Saturday January 17, 2015
Status: Moderator
Last seen: September 30, 2021

- 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)?

Excel if cell contains date then return value
Excel if cell contains text then return value in another cell - Best answers
If cell contains 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 cell contains text then return value multiple ✓ - Forum - Excel
Excel "IF" function w/ date in test cell ✓ - Forum - Excel
Excel if range of cell contain a text put a cell value in ✓ - Forum - Excel

2 replies

Answer 1 / 2

BrianGreen

Posts: 1010
Registration date: Saturday January 17, 2015
Status: Moderator
Last seen: September 30, 2021

148
Nov 12, 2015 at 05:44 PM

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.

JerrySD

Posts: 2
Registration date: Thursday November 12, 2015
Status: Member
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.

Answer 2 / 2

RayH

Posts: 122
Registration date: Tuesday August 31, 2010
Status: Contributor
Last seen: June 20, 2016

25
Nov 12, 2015 at 07:37 PM

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: 1010
Registration date: Saturday January 17, 2015
Status: Moderator
Last seen: September 30, 2021

148
Nov 13, 2015 at 12:42 PM

Thanks Ray :)

Similar discussions

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

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)

Similar discussions

Subscribe To Our Newsletter!