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

Question

Previous Topic Next Topic

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

Report

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: 1004
Registration date: Saturday January 17, 2015
Status: Moderator
Last seen: November 27, 2020

- 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 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
If cell A1 has text then cell B2 has today's Date ✓ - Forum - Excel
Excel - IF function with multiple words ✓ - Forum - Excel
If a cell contains any text then add a value (with dates) ✓ - Forum - Excel

2 replies

Answer 2 / 2

RayH

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

24
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.

Report

BrianGreen

Posts: 1004
Registration date: Saturday January 17, 2015
Status: Moderator
Last seen: November 27, 2020

135
Nov 13, 2015 at 12:42 PM

Thanks Ray :)

Ask a question

BrianGreen · Accepted Answer · 2015-11-12T17:44:03-0500

Answer 1 / 2

BrianGreen

Posts: 1004
Registration date: Saturday January 17, 2015
Status: Moderator
Last seen: November 27, 2020

135
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.

Report

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.

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

2 replies

Your reply

Subscribe To Our Newsletter!