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

 

[DanteAmor]

Could you continue the example A4 = 1/1/2019, A5 = 12/28/2018, for example up to A12 and then explain what you need in J4?

 

[jarhead58]

Could you continue the example A4 = 1/1/2019, A5 = 12/28/2018, for example up to A12 and then explain what you need in J4?

Thanks for the reply! The cells in column A are random dates entered sorted from newest to oldest; A6 = 12/26/18, A7 = 12/20/18 and so on!

 

[gaz_chops]

Maybe

=MODE(OFFSET(B4,,,MATCH(A4,A:A,-1)))

 

[jarhead58]

Maybe

=MODE(OFFSET(B4,,,MATCH(A4,A:A,-1)))

Result is #N/A

 

[DanteAmor]

Thanks for the reply! The cells in column A are random dates entered sorted from newest to oldest; A6 = 12/26/18, A7 = 12/20/18 and so on!

How can I change the value of J4 ":B105" to the column B cell number closest to a year from the date of A4

I need you to give the example of the result you expect

 

[gaz_chops]

Formula should have been

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

if you change MODE to SUM

=SUM(OFFSET(B4,,,MATCH(A4,A5:A10000,-1)))

what do you get?

 

[jarhead58]

Formula should have been

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

if you change MODE to SUM

=SUM(OFFSET(B4,,,MATCH(A4,A5:A10000,-1)))

what do you get?

gaz-chops, I still get #N/A with both

 

[gaz_chops]

Do some of the cells in Col B have #N/A?

 

[jarhead58]

I need you to give the example of the result you expect

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?