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

 

[jarhead58]

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

Thanks for the reply! Just an fyi, which I should have mentioned, the other columns just contain numbers like in Column A! Would the EDate function still be necessary for C, D, E, and F? TIA

 

[gaz_chops]

The Match(Edate part determines how many rows of data to be included, for each column.

What you could do (which i would do), is to put this part into a separate cell, then reference that cell, like

In Cell D1 put
=MATCH(EDATE(A4,-12),A4:A10000,-1)

Then your formula can reference that cell, like
=MODE(OFFSET(B4,,,D1),OFFSET(C4,,,D1),OFFSET(D4,,,D1),OFFSET(E4,,,D1),OFFSET(F4,,,D1))

hth
Gaz

 

[jarhead58]

The Match(Edate part determines how many rows of data to be included, for each column.

What you could do (which i would do), is to put this part into a separate cell, then reference that cell, like

In Cell D1 put
=MATCH(EDATE(A4,-12),A4:A10000,-1)

Then your formula can reference that cell, like
=MODE(OFFSET(B4,,,D1),OFFSET(C4,,,D1),OFFSET(D4,,,D1),OFFSET(E4,,,D1),OFFSET(F4,,,D1))

hth
Gaz

This would work but after looking at the rest of my spreadsheet, I have so much going on with different formulas and references that it would probably be better left alone as is! If it ain't broke don't fix it lol! I was just trying to cut some of my manual entries down but that's ok, I can do this lol!! I'm sorry if I wasted your time in the process! Thank you so much for all of your help gaz, you were a great help!!

 

[gaz_chops]

You're welcome, no problem at all, better to look at dif options.

Just to clarify, you said "the other columns just contain numbers like in Column A", doesn't Col A contain dates? And the other columns just numbers! Just worried that I may have got the wrong end of the stick somewhere.

Gaz

 

[jarhead58]

You're welcome, no problem at all, better to look at dif options.

Just to clarify, you said "the other columns just contain numbers like in Column A", doesn't Col A contain dates? And the other columns just numbers! Just worried that I may have got the wrong end of the stick somewhere.

Gaz

See, I told you I was old lol!! You're correct, it should have been like Column B!

 

[gaz_chops]

lol all good then