# Date Function

#### dleitch70

##### New Member
Hi All,

I'm trying to figure out the best way to do the following:

I have 12 cells (one for each month). In the first cell (January) I will have a % complete value, in the next field to the right I will have a % complete for February and so on.

I need a function that will look at current date (i.e. TODAY(), and if today is greater than January 31st, I want the function to return the value for the % complete in February. I need this to repeat; meaning if the current date is > than Feb 28, I want it to return the value from the 3rd field (% complete for March), and so on.

Something like:
=IF(TODAY()>(5/30/2015),M27,IF(TODAY()>(6/30/2015),O27, "And so on). There has to be a better why hand repeating the IF(TODAY()>xxx function over and over. Any suggestions?

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### Special-K99

##### Well-known Member
If you've got more than 5 IF's in a formula then you should be considering a lookup table

Best post a sample file with your input data and your expected output

You cant attach files on this forum.
Upload the file to an online storage site then place a link to it on this forum.

#### jasonb75

##### Well-known Member
Try

=INDEX(B27:Y27,MONTH(TODAY())*2)

Based you your example, I've assumed that January is in C27, December in Y27.

#### dleitch70

##### New Member
Try

=INDEX(B27:Y27,MONTH(TODAY())*2)

Based you your example, I've assumed that January is in C27, December in Y27.

Hi All,

The example is this:

For each month there are 2 columns (Billed (Column B) & % Earned (Column C), June is Column D & E and so on, as seen below.

May June July
Billed % Earned Billed % Earned Billed % Earned
10 20 30

If the current date is before the end of May I want to pull the value (10) from column C into column A. If the current date is after the end of May but before the end of June, I want to pull the value (20) from column E into column A...and so on.

Any advice on the best way to accomplish that?

#### gaz_chops

##### Well-known Member
Try

=INDEX(C1:Y1,,MATCH(MONTH(TODAY()),MONTH(B1:X1)))

Entered as an array (Ctrl, Shift & Enter)

#### dleitch70

##### New Member
I entered it as follows:

=INDEX(L29:AH29,,MATCH(MONTH(TODAY()),MONTH(K29:AG29)))

It's basically the same, only coordinates are different. Unfortunately it give me a #VALUE! error.

#### gaz_chops

##### Well-known Member
You must enter as an array, double click the cell to edit the formula, then press and hold Control & Shift buttons and press Enter.

#### jasonb75

##### Well-known Member
A non array method

=INDEX(C27:Y27,(MOD(MONTH(TODAY())+7,12)*2+1))

#### gaz_chops

##### Well-known Member

=INDEX(C1:Y1,,MATCH(MONTH(A1),MONTH(B1:X1),0))

#### dleitch70

##### New Member
Thanks guys! I got it working.

Jason, sorry, one thing I forgot. Once the % Earned reached a 100 I don't want it to take the next value. For example if the % Earned is 80% in May, and it's May, I want it to grab the 80%. If it's now June and the % Earned is 90% I want it to grab this value. If in July the % Earned for July is 100%, I want to grab this value; however, if it now August, the % Earned will be 0 because we reached 100% in July, so I don't want it to grab the % Earned for August, September, etc.

So your function works to grab the subsequent values, but I need it to stop once it finds a % Earned of 100.

Thanks!!!

Replies
1
Views
169
Replies
1
Views
259
Replies
2
Views
229
Replies
7
Views
1K
Replies
10
Views
914

1,195,704
Messages
6,011,206
Members
441,594
Latest member
AVO

### 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.

### Which adblocker are you using?

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

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