Microsoft Excel 2010 - complete product...

I need to return a number associated with a date based on today's date. For example: A1:A24 are numbers 1 to 24 and B1:B24 contain the 15th and 30th for each month of the year; 15-Jan, 30-Jan, 15-Feb, 28-Feb, ... 30-Dec. Cell C1 needs to return the number from A1:A24 based upon today's date compared to the date in B1:B24.

For example today is 08/21/15, what number should be returned in Cell C1. It should be 15 as 08/21 is less than 30-Aug.

message edited by jmichels

Try this formula, see how it work for you: =INDEX(A1:B24,MATCH(TODAY(),B1:B24,1),1)

This will work for most dates, but errors out if the Date is 01/14/15 or less.

MIKE

message edited by mmcconaghy

Here is a modified version that should work for all dates: =IF(AND(MONTH(TODAY())=1,DAY(TODAY())<15),1,INDEX(A1:B24,MATCH(TODAY(),B1:B24,1),1))

If the Date is less than Jan 15 it returns 1

MIKE

message edited by mmcconaghy

Mike - Both solutions worked perfectly with the conditions in my question. I'll have to wait till the end of the month to see if the 15 turns into a 16. Thanks for the syntax and all the hard work figuring out the solutions!!!!

Ask Your Question

Weekly Poll