Excel: If Date =, then enter a value

Solved/Closed

JerrySD Posts 2 Registration date Thursday 12 November 2015 Status Member Last seen 12 November 2015 - 12 Nov 2015 à 16:53
BrianGreen Posts 1005 Registration date Saturday 17 January 2015 Status Contributor Last seen 30 September 2021 - 13 Nov 2015 à 12:42

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

Dash becomes date on excel
Excel if date - Best answers
If date formula in excel - Best answers
Date formatting in dd/mm/yyyy ✓ - Office Software Forum
Geometry dash free download - Download - Platform
Danger dash unlock code ✓ - Phones, PDA & GPS Forum
How to unlock passcode - Phones, PDA & GPS Forum
Unlock code ✓ - Phones, PDA & GPS Forum

2 responses

Réponse 1 / 2

BrianGreen Posts 1005 Registration date Saturday 17 January 2015 Status Contributor Last seen 30 September 2021 149
12 Nov 2015 à 17:44

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 12 November 2015 Status Member Last seen 12 November 2015
12 Nov 2015 à 18:10

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.

Réponse 2 / 2

RayH Posts 122 Registration date Tuesday 31 August 2010 Status Contributor Last seen 20 June 2016 26
12 Nov 2015 à 19:37

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 1005 Registration date Saturday 17 January 2015 Status Contributor Last seen 30 September 2021 149
13 Nov 2015 à 12:42

Thanks Ray :)

Similar discussions

Excel if range of cell contain a text put a cell value in

If cell = date return yes or no.

If a cell contains any text then add a value

Compare values in two columns and return the value from third

Highlight cell if another cell contains any text

Conditional formatting: change fontcolor based on another cell

Similar discussions

Subscribe To Our Newsletter!