Formula to find date in row = current date and then cal.

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
Hi,

I have a question for the formula gurus of the forum.

I have the following on my sheet.

How would I construct a formula that would look at row G131:R131 and find the date (month) that matches the current date (month). Then become the value of the cell that is 2 cells above the cell that it found which is the current month?

For example: today the formula would be = 977.07. The formula would see that it is October and choose cell G131 because it is currently Oct and then look 2 cells up and find 977.07.

Thanks
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
that formula should work if your dates are 1st of the month formatted as "mmm-yy".

If they're not and you only have one of each month (i.e. you don't care about the year) then the following array formula, confirmed with CTRL+SHIFT+ENTER should work for you


=INDEX(G129:R129,MATCH(MONTH(TODAY()),MONTH(G131:R131),0))
 

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
I use the format that you specify but I get the dates with the following formula:

=EDATE(start,0)

then

=EDATE(start,1)

and so on.

The months are never repeated.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

Didn't the last formula work?

what's start is that equal to TODAY()?
 

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064

ADVERTISEMENT

And with your last formula all I get is #N/A

With you forst formula, it works until I change start to 11/1/2005, then I get #N/A
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
If you have start as 1st November 2005 then your october date will be Oct-06 right?

Do you still want to pick up the october value if it's next year's?

either way this should work as long as there is an october date (any year) in the G131:R131 range it should give the corresponding value of row 129

=INDEX(G129:R129,MATCH(MONTH(TODAY()),MONTH(G131:R131),0))

have you confirmed with CTRL+SHIFT+ENTER?
 

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
Yes that would make the Oct date 06. I just want a formula that looks to see in the month in the column is = to the current month. The year should be ignored completely.

What do you mean by CTRL+SHIFT+ENTER?

All I get is a #N/A using that formula?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
If you already have the formula in a cell click somewhere in the formula in the formula bar and then hold down CTRL and SHIFT buttons while hitting ENTER.

This puts curly braces around the formula in the formula bar

normally if you haven't done this you get a #VALUE! error not #N/A, please check that you're using the last formula I quoted above
 

Watch MrExcel Video

Forum statistics

Threads
1,128,167
Messages
5,629,079
Members
416,364
Latest member
maatpsr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top