Simply formula

[Corleone]

This one is just out of interest
A colleague has sent over a spreadsheet which generates timelines based on dates contained in cols B - F
It used a series of dates at the top in row 2 from col g which are set up in 14 day intervals and then the rows below contain the formula below in order to populate the cells with specific characters depending on the dates in Cols B-F
Im sure there must be a simplified formula which will perform the same output

=IF(IFERROR(OR(I$2-$B6=-13,I$2-$B6=-12,I$2-$B6=-11,I$2-$B6=-10,I$2-$B6=-9,I$2-$B6=-8,I$2-$B6=-7,I$2-$B6=-6,I$2-$B6=-5,I$2-$B6=-4,I$2-$B6=-3,I$2-$B6=-2,I$2-$B6=-1,I$2-$B6=0),""),CHAR(186),IF(IFERROR(OR(I$2-$C6=-13,I$2-$C6=-12,I$2-$C6=-11,I$2-$C6=-10,I$2-$C6=-9,I$2-$C6=-8,I$2-$C6=-7,I$2-$C6=-6,I$2-$C6=-5,I$2-$C6=-4,I$2-$C6=-3,I$2-$C6=-2,I$2-$C6=-1,I$2-$C6=0),""),CHAR(190),IF(IFERROR(OR(I$2-$D6=-13,I$2-$D6=-12,I$2-$D6=-11,I$2-$D6=-10,I$2-$D6=-9,I$2-$D6=-8,I$2-$D6=-7,I$2-$D6=-6,I$2-$D6=-5,I$2-$D6=-4,I$2-$D6=-3,I$2-$D6=-2,I$2-$D6=-1,I$2-$D6=0),""),CHAR(191),IF(IFERROR(OR(I$2-$E6=-13,I$2-$E6=-12,I$2-$E6=-11,I$2-$E6=-10,I$2-$E6=-9,I$2-$E6=-8,I$2-$E6=-7,I$2-$E6=-6,I$2-$E6=-5,I$2-$E6=-4,I$2-$E6=-3,I$2-$E6=-2,I$2-$E6=-1,I$2-$E6=0),""),CHAR(192),IF(IFERROR(OR(I$2-$F6=-13,I$2-$F6=-12,I$2-$F6=-11,I$2-$F6=-10,I$2-$F6=-9,I$2-$F6=-8,I$2-$F6=-7,I$2-$F6=-6,I$2-$F6=-5,I$2-$F6=-4,I$2-$F6=-3,I$2-$F6=-2,I$2-$F6=-1,I$2-$F6=0),""),CHAR(187),"")))))



any ideas?

 

[Fluff]

One option would be like

=IF(MEDIAN(I2,B6,I2+13)=B6,CHAR(186),IF(MEDIAN(I2,C6,I2+13)=C6,CHAR(190)))

 

[Corleone]

Spot on!

Cheers

 

[Fluff]

Glad to help & thanks for the feedback.

 

[FormR]

Here's another option for G4 copied down and across.

=IFNA(CHAR(CHOOSE(MATCH(1,COUNTIFS(G$2,"<="&$B4:$F4,G$2,">"&$B4:$F4-13),0),186,190,191,192,187)),"")

 

[FormR]

Or another one you could try:

=FILTER(CHAR({186,190,191,192,187}),(G$2<=$B4:$F4)*(G$2>$B4:$F4-13),"")

 

[Corleone]

Thanks for all your responses - Much appreciated
Heres another one for you

Is there a way of performing the same result purely based on using the dates in the date header rather than specifying the -13 - for example If this report was to be modified so that in some instances the difference between the dates running from left to right at the top were not always the same (so in stead of a constant -13 it would be displaying the character based on it occurring after $I$2 but before or on the date in $I$3

Cheers

 

[FormR]

Hi, if the interval is consistent across all of the dates then you could determine the interval based on the difference between the first 2 dates, like this:

=FILTER(CHAR({186,190,191,192,187}),(G$2<=$B4:$F4)*(G$2>$B4:$F4-($H$2-$G$2)),"")

If it's not consistent, then you need to define how to determine what the interval of the date in the last column is.

If might make more sense to have two rows of dates specifying both the start and end date - something like this:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
2					Start	05-Sep-2022	19-Sep-2022	03-Oct-2022	17-Oct-2022	31-Oct-2022	14-Nov-2022	28-Nov-2022
3					End	18-Sep-2022	02-Oct-2022	16-Oct-2022	30-Oct-2022	13-Nov-2022	27-Nov-2022	11-Dec-2022
4	01-May-2023	28-May-2024	22-Jul-2024	12-Aug-2024	21-Oct-2024
5	06-Feb-2023	28-May-2024	22-Jul-2024	12-Aug-2024	21-Oct-2024
6	19-Sep-2022	10-Oct-2022	02-Dec-2022	09-Jan-2023	20-Feb-2023		º	¾				¿
Sheet1

Cell Formulas
Range	Formula
G4:M6	G4	=FILTER(CHAR({186,190,191,192,187}),(G$2<=$B4:$F4)*(G$3>=$B4:$F4),"")

 

[Corleone]

Thats works perfectly - Thanks

Ive never come across this filter formula before could you breakdown what it is doing?

Thanks again

 

[FormR]

Hi, check out the MS help:

FILTER function - Microsoft Support

How to use the FILTER function in Excel to filter a range of data based on criteria you define.

support.microsoft.com

It's really designed to return multiple values - In this instance we are relying on the fact the dates in columns B:F are unique across each row to return just one of CHAR()'s based on the criteria specified in the second argument.