Reference Dates

There are quite a few ways to reference dates.

Assuming we have dates of a year in cells A1:A366 and corresponding information in cells B1:B366, this formula can be used to look up today's date.

=SUMPRODUCT((MONTH(A1:A366)=MONTH(TODAY()))*(DAY(A1:A366)=DAY(TODAY()))*B1:B366)

Note: If Column B did not have numerical values the above formula would return a #VALUE! error. The following formula work better in this case.

{=INDEX(B1:B366,MATCH(1,(MONTH(A1:A366)=MONTH(TODAY()))*(DAY(A1:A366)=DAY(TODAY())),0))}

Must be entered as an array. (Push Ctrl, Shift and Enter simultaneously)



© Copyright andrewsexceltips.net All Rights Reserved.