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

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History