Date Function

[dleitch70]

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?

 

[Special-K99]

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]

Try

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

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

 

[dleitch70]

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]

Try

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

Entered as an array (Ctrl, Shift & Enter)

 

[dleitch70]

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]

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]

A non array method

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

 

[gaz_chops]

Realised I made a mistake!

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

 

[dleitch70]

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!!!