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]

Amended to look at minus 1 year

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

 

[jarhead58]

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

No

 

[gaz_chops]

Try the amended formula

 

[jarhead58]

Amended to look at minus 1 year

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

Hmm...it gives me the value of J4 in the cell with this formula. I might be going about this the wrong way trying to explain this! The whole Mode(B4:B105) is just to find the number most indicated in that range. B105, I would like to change to B104 or B106, 7 whatever the row A104, A106, 7 value is in. For eg. if A4 is 1/1/19 and the closet date to it from a year ago is 1/3/18 and it's value is in A106 then I would like to have the B105 value to coincide with the A value, B106. Have I confused you entirely lol?

 

[gaz_chops]

That should be what it is doing.

The Match(Edate part matches the date in A4 (minus 12 mths), to the dates in range A5:A10000.
It returns the row number of the date that is closest (Same or greater than A4).
It uses this row number to create the range for Col B, e.g. B4:B106 (if row 106 is where it found the date match).

hth

Gaz

 

[jarhead58]

That should be what it is doing.

The Match(Edate part matches the date in A4 (minus 12 mths), to the dates in range A5:A10000.
It returns the row number of the date that is closest (Same or greater than A4).
It uses this row number to create the range for Col B, e.g. B4:B106 (if row 106 is where it found the date match).

hth

Gaz

Here is a link to the test spreadsheet. https://1drv.ms/x/s!Apokzs4VeX3NtXXxDqV5WIW0iJgg

 

[gaz_chops]

Is the result wrong?

 

[jarhead58]

Is the result wrong?

Yes, the B105 should change to B107, follow me?

Edited: The value of 6 will change eventually as the entries go on. I just need the formula to correct itself, if possible to fit the new date position.

 

[gaz_chops]

It does, in my formula!

 

[jarhead58]

It does, in my formula!

Old guy moment!! Im still worried about the actual J4 formula changing physically when you are actually replacing J4 with yours in L4!! Ok, I'm officially embarrassed lol!!