Solved Return a number based on today's date compared another date.

Microsoft Excel 2010 - complete product...
August 21, 2015 at 21:51:01
Specs: Windows 10
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


See More: Return a number based on todays date compared another date.

Report •


#1
August 22, 2015 at 06:52:00
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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
August 22, 2015 at 06:57:36
✔ Best Answer
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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#3
August 24, 2015 at 19:59:05
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!!!!


Report •

Related Solutions


Ask Question