SOLVED

Formula with Multiple Date Ranges

%3CLINGO-SUB%20id%3D%22lingo-sub-1761662%22%20slang%3D%22en-US%22%3EFormula%20with%20Multiple%20Date%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1761662%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20figure%20out%20a%20formula%20for%20when%20a%20%3CU%3Edate%3C%2FU%3E%20within%20a%20certain%20range%20is%20entered%20in%20one%20cell%2C%20a%20certain%20character%20is%20produced%20in%20another%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3EI%20want%20for%20the%20date%20range%20of%2010%2F19%2F2020%20-%2010%2F25%2F2020%20to%20produce%2011%20in%20one%20cell.%3C%2FP%3E%3CP%3EAnd%20when%20the%20date%20range%20is%2010%2F26%2F2020%20-%2011%2F01%2F2020%2C%20I%20want%20it%20to%20produce%2018%20in%20the%20cell.%3C%2FP%3E%3CP%3EThen%20when%20the%20date%20range%20is%2011%2F02%2F2020%20-%2011%2F08%2F2020%2C%20I%20want%20it%20to%20produce%2025%20in%20the%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20so%20on.%20I%20have%20many%20date%20ranges%20that%20need%20to%20produce%20new%20numbers.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1761662%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1761797%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20with%20Multiple%20Date%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1761797%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F824136%22%20target%3D%22_blank%22%3E%40taylorcobaugh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3EI%20hope%20the%20following%20formula%20will%20help%20you%20to%20solve%20this%20issue%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(A2%26gt%3B%3DDATE(2020%2C10%2C19)%2C%20B2%26lt%3B%3DDATE(2020%2C10%2C25))%2C%2011%2C%20IF(AND(A2%26gt%3B%3DDATE(2020%2C10%2C26)%2C%20B2%26lt%3B%3DDATE(2020%2C11%2C1))%2C%2018%2C%20IF(AND(A2%26gt%3B%3DDATE(2020%2C11%2C2)%2C%20B2%26lt%3B%3DDATE(2020%2C11%2C8))%2C25%2C%22%22)))%3C%2FP%3E%3CP%3ECell%20A%20-%20beginning%20date%20of%20range%20where%20Cell%20B%20shows%20ending%20date%20of%20date%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20note%20attached%20file%20for%20your%20further%20consideration.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20consider%20this%20response%20your%20best%20one%2C%20please%20note.%3C%2FP%3E%3CP%3EGood%20luck.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1761838%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20with%20Multiple%20Date%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1761838%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F481821%22%20target%3D%22_blank%22%3E%40Ilgar_Zarbaliyev%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Edgar%2C%20thank%20you%20for%20your%20response.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20actually%20trying%20to%20get%20a%20formula%20for%20one%20cell%20only.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20I%20currently%20have%20for%20the%20cell%20is%3A%3C%2FP%3E%3CP%3E%3DIF(AND(%24BK%244%26gt%3B%3DDATEVALUE(%2210%2F19%2F2020%22)%2C%24BK%244%26lt%3B%3DDATEVALUE(%2210%2F25%2F2020%22))%2C%2211%22%2C%22%22)%3C%2FP%3E%3CP%3EThis%20formula%20populates%20%2211%22%20when%20someone%20enters%20a%20date%20between%20the%20date%20range%2010%2F19%20-%2010%2F25.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20I%20need%20to%20make%20it%20so%20that%20the%20cell%20will%20also%20populate%20a%20different%20number%2C%20when%20a%20date%20in%20a%20different%20range%20is%20entered.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EExample%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EA%20date%20entered%20between%2010%2F19%2F20%20-%2010%2F25%2F20%20will%20populate%2011%3C%2FP%3E%3CP%3EA%20date%20entered%20between%2010%2F26%2F20%20-%2011%2F01%2F20%20will%20populate%2018%3C%2FP%3E%3CP%3EA%20date%20entered%20between%2011%2F02%2F20%20-%2011%2F08%2F20%20will%20populate%2026%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20needing%20code%20for%20all%20of%20this%20for%20the%20same%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20my%20document%20with%20the%202%20cells%20I%20am%20working%20with%20highlighted%20in%20Green.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1761850%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20with%20Multiple%20Date%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1761850%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F824136%22%20target%3D%22_blank%22%3E%40taylorcobaugh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3EActually%2C%20my%20formula%20is%20also%20for%20one%20cell%20result%20oriented.%20Just%20%2C%20it%20has%20been%20written%20in%20three%20rows%20in%20my%20previous%20response%20message%20box.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Ilgar_Zarbaliyev_1-1602192616560.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F225216i273A396FFC016136%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Ilgar_Zarbaliyev_1-1602192616560.png%22%20alt%3D%22Ilgar_Zarbaliyev_1-1602192616560.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I need to figure out a formula for when a date within a certain range is entered in one cell, a certain character is produced in another cell.

 

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.

 

And so on. I have many date ranges that need to produce new numbers.

6 Replies
Highlighted

@taylorcobaugh 

Hi there,

I hope the following formula will help you to solve this issue:

 

=IF(AND(A2>=DATE(2020,10,19), B2<=DATE(2020,10,25)), 11, IF(AND(A2>=DATE(2020,10,26), B2<=DATE(2020,11,1)), 18, IF(AND(A2>=DATE(2020,11,2), B2<=DATE(2020,11,8)),25,"")))

Cell A - beginning date of range where Cell B shows ending date of date range.

 

Please note attached file for your further consideration.

 

If you consider this response your best one, please note.

Good luck.

 

 

Highlighted

@Ilgar_Zarbaliyev 

Hi Edgar, thank you for your response. 

I am actually trying to get a formula for one cell only. 

 

The formula I currently have for the cell is:

=IF(AND($BK$4>=DATEVALUE("10/19/2020"),$BK$4<=DATEVALUE("10/25/2020")),"11","")

This formula populates "11" when someone enters a date between the date range 10/19 - 10/25.

 

Now I need to make it so that the cell will also populate a different number, when a date in a different range is entered.

 

Example:

A date entered between 10/19/20 - 10/25/20 will populate 11

A date entered between 10/26/20 - 11/01/20 will populate 18

A date entered between 11/02/20 - 11/08/20 will populate 26

 

I am needing code for all of this for the same cell.

 

I have attached my document with the 2 cells I am working with highlighted in Green.

 

Highlighted
Best Response confirmed by taylorcobaugh (Occasional Contributor)
Solution

@taylorcobaugh 

 

Hi there,

Actually, my formula is also for one cell result oriented. Just , it has been written in three rows in my previous response message box.

 

Ilgar_Zarbaliyev_1-1602192616560.png

 

 

Highlighted

@taylorcobaugh 

That could be

=IFERROR(INDEX({11,18,26},INT(($BK$4-DATE(2020,10,19))/7)+1),"")

In general that's not a good idea to hardcode dates within all formulas. Better to have only one starting date or month/year and make all calculations based on it. Includes weekdays names (M,T,W,...)

Highlighted

@Ilgar_Zarbaliyev 

You are right this works!

Trying it with DATEVALUE doesn't work but it works with DATE.

 

Thank you!

Highlighted
Here you are!