F
Fred
I have a table , dimensions A3:AL17
Column A is the owner and columns B:E are info relating to the owner
Cells F3:AL3 (column headers) are weekly dates (from 11-May through to
21-Dec), custom formatted dd-mmm
Cells F4:AL17 will be blank or contain either "X" or "Hols"
I am trying to calculate the number of weeks since the last "X" in
each row of the table in relation to today's date.
For example, I have the following to get the difference between a
fixed date (column H) and today's date in Weeks
=TRUNC((TODAY()-H$3)/7)&" Weeks", where H3 contains 25-May I get the
answer 3 Weeks
and an array formula to get the last instance of "X" on a specific
row,
=(INDEX(F$3:AL$3,MAX((F4:AL4="X")*(COLUMN(F$4:AL$4)-5)))) , where
there is "X" in columns G (18-May) and J (08-Jun) I get the answer 08-
Jun
however when I try to combine them, I get a value of 34891 weeks
instead of the 1 week I am expecting
=TRUNC((TODAY()-(INDEX(F$3:AL$3,MAX((F4:AL4="X")*(COLUMN(F$4:AL
$4)-5))))/7))&" Weeks"
Any help gratefully received
Regards
Fred
Column A is the owner and columns B:E are info relating to the owner
Cells F3:AL3 (column headers) are weekly dates (from 11-May through to
21-Dec), custom formatted dd-mmm
Cells F4:AL17 will be blank or contain either "X" or "Hols"
I am trying to calculate the number of weeks since the last "X" in
each row of the table in relation to today's date.
For example, I have the following to get the difference between a
fixed date (column H) and today's date in Weeks
=TRUNC((TODAY()-H$3)/7)&" Weeks", where H3 contains 25-May I get the
answer 3 Weeks
and an array formula to get the last instance of "X" on a specific
row,
=(INDEX(F$3:AL$3,MAX((F4:AL4="X")*(COLUMN(F$4:AL$4)-5)))) , where
there is "X" in columns G (18-May) and J (08-Jun) I get the answer 08-
Jun
however when I try to combine them, I get a value of 34891 weeks
instead of the 1 week I am expecting
=TRUNC((TODAY()-(INDEX(F$3:AL$3,MAX((F4:AL4="X")*(COLUMN(F$4:AL
$4)-5))))/7))&" Weeks"
Any help gratefully received
Regards
Fred