Oct 07 2020 03:05 PM
I would like for an "X" to appear in a cell when certain date ranges are entered in another cell.
I currently have a formula for the X to appear when the date range 10/19/2020 - 10/25/2020 is entered, but I don't know how to add multiple date ranges to the formula. The current formula I have is:
=IF(AND($BK$4>=DATEVALUE("10/19/2020"),$BK$4<=DATEVALUE("10/25/2020")),"X","")
I need to add a few more date ranges, one including 11/01/2020 - 11/06/2020.
Oct 07 2020 10:06 PM
Solution
Try this one:
=IF(OR(AND(A1>=DATEVALUE("10/19/2020"), A1<=DATEVALUE("10/25/2020")), AND(A1>=DATEVALUE("11/01/2020"), A1<=DATEVALUE("11/06/2020"))),"x","")
Oct 07 2020 11:17 PM
@taylorcobaugh As a variant, have a look at the attached workbook. Include the star and end dates in two named ranges (start and end) and use a formula like:
=IF(SUM((A10>=start)*(A10<=end)),"X","")
where A10 holds the date you want to test. No need for nested IFS and particularly handy when you need to add more than just a few date ranges. Obviously, your dates have to be real dates, not texts looking like dates.
Oct 07 2020 11:27 PM
Oct 07 2020 11:37 PM - edited Oct 07 2020 11:42 PM
@Rajesh_Sinha All depends on your Excel version. I'm on the most recent one and CSE is never an issue. It is my understanding that Excel silently converts such formulae to CSE style in older versions. But perhaps I'm mistaken. Then, indeed, such formulae should be entered with CSE.
Oct 08 2020 05:48 AM - edited Oct 08 2020 05:51 AM
If you are talking about this =ArrayFormula(SUM(A1:A5 * B1:B5)) then, this happens in Google Sheet only,,, where user has to use either "ArrayFormula" the reserve word or even executes CSE ,, Google Sheet adds ArrayFormula with the formula!!
With Excel 2016, 2019 & 365 Dynamic array formula has been introduced but list is very small and doesn't covers the formula U have used.
Now check the File you have uploaded ,,,, I've examined and returning to you ,,, no where I found EXCEL automatically applied CSE ,,, Ctrl+Shift+Enter !! And I've opened it with 2013.
Oct 08 2020 09:44 AM
@Rajesh_Sinha Thank you this answer did work.
Now I need to figure out how the formula for when multiple different date ranges produce different responses.
Example:
I want for the date range of 10/19/2020 - 10/25/2020 to produce 11 in one cell.
And when the date range is 10/26/2020 - 11/01/2020, I want it to produce 18 in the cell.
Then when the date range is 11/02/2020 - 11/08/2020, I want it to produce 25 in the cell.
Can you help me with this?
Oct 08 2020 11:37 AM
@taylorcobaugh Oh, you change the rules a bit now, but that can be dealt with. See attached (in the green shaded area).
Oct 08 2020 12:08 PM
Thank you for sending that over. I think that the situation I have is a bit different.
I attached my document, it is the first tab titled "WE 8-17".
There is one spot where a user can enter the date, and if that date falls within certain date ranges, I want a certain number to appear.
So every time someone types in a date to a new week, the dates in Row 10 change to next week's dates.
I hope this makes sense.
Oct 08 2020 01:58 PM - edited Oct 08 2020 02:00 PM
Someone might well have suggested a table to contain the date ranges (I haven't opened all the files)
If you wish to return IDs for the ranges then the formula extends to
= IFERROR(
IF(LOOKUP(date, startRange, endRange) >= date,
LOOKUP(date, startRange, rangeID),
"-" ),
"-")
replacing the "X" by a further lookup.
Oct 08 2020 08:53 PM
You may use this:
=IF(AND(A1>=DATE(2020,10,19),A1<=DATE(2020,10,25)),11,IF(AND(A1>=DATE(2020,10,26),A1<=DATE(2020,11,1)),18,IF(AND(A1>=DATE(2020,11,2),A1<=DATE(2020,11,8)),25,0)))
Oct 13 2020 05:22 PM
If i have a number, like 3 in a cell, then a want a particular formula for occur for three years, if if have a 4 in a particular cell i would like another particular formula to occur in this cell. How do I do this?
Jim
Oct 14 2020 10:52 AM
As variant you may use IF() or CHOOSE() or like, but depends on what exactly do you mean.
Oct 07 2020 10:06 PM
Solution
Try this one:
=IF(OR(AND(A1>=DATEVALUE("10/19/2020"), A1<=DATEVALUE("10/25/2020")), AND(A1>=DATEVALUE("11/01/2020"), A1<=DATEVALUE("11/06/2020"))),"x","")