Year from date

[jarhead58]

Hey everyone!! Hope you all had a great holiday season!!

I have J4 set with "=Mode(B4:B105)" which column B contain various numbers. Column A cells are dates, (A4=1/1/2019, A5=12/28/2018....etc going backward) How can I change the value of J4 ":B105" to the column B cell number closest to a year from the date of A4 or is it not possible? TIA

 

[gaz_chops]

lol np, so it appears to work ok?

 

[jarhead58]

lol np, so it appears to work ok?

It appears to be! Thanks again for all of the help and "Patience" with this old guy lol!!

 

[DanteAmor]

Say A4 has 1/2/19, I'm looking for the closest same date except for it being a year ago like 1/2 or 1/4/2018 within 2 days of A4 date! Does that help?

Go the formula:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]D4[/TD]
[TD]=MODE(INDIRECT("B4:B"&MATCH(DATE(YEAR(A4)-1,MONTH(A4),DAY(A4)),A1:A400,-1)))[/TD]
[/TR]
</tbody>[/TABLE]

 

[gaz_chops]

You're welcome, from another old guy

 

[gaz_chops]

Go the formula:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]D4[/TD]
[TD]=MODE(INDIRECT("B4:B"&MATCH(DATE(YEAR(A4)-1,MONTH(A4),DAY(A4)),A1:A400,-1)))[/TD]
[/TR]
</tbody>[/TABLE]

Think the range may be wrong
A1:A400 > A4:A400

 

[jarhead58]

DanteAmor, thanks for the help my friend but we got it working now!!

 

[DanteAmor]

Think the range may be wrong
A1:A400 > A4:A400

It need the row number, that's why the range should start at 1

 

[DanteAmor]

DanteAmor, thanks for the help my friend but we got it working now!!

Your welcome

 

[jarhead58]

Amended to look at minus 1 year

=MODE(OFFSET(B4,,,MATCH(EDATE(A4,-12),A5:A10000,-1)))

Hey gaz_chops, what if I wanted to include other columns, say B4:F105 rather than(B4,,,MATCH......, asking for a friend lol?

 

[gaz_chops]

Hi,

Try repeating the the Offset formula, changing the "B" to be the NEW column to include.

=MODE(OFFSET(B4,,,MATCH(EDATE(A4,-12),A4:A10000,-1)),OFFSET(C4,,,MATCH(EDATE(A4,-12),A4:A10000,-1)),OFFSET(D4,,,MATCH(EDATE(A4,-12),A4:A10000,-1)),OFFSET(E4,,,MATCH(EDATE(A4,-12),A4:A10000,-1)),OFFSET(F4,,,MATCH(EDATE(A4,-12),A4:A10000,-1)))

Gaz