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?

 

[gaz_chops]

You're welcome.

 

[dleitch70]

Hi gaz_chops

Thanks for the correction. I was wondering why I couldn't get it to work. It works now...sort of. I entered it as an array, as you suggested. What I'm not sure about is the (A1). The function is as follows:

{=INDEX(L28:AH28,,MATCH(MONTH(I28),MONTH(K28:AG28),0))}

As you can see I changes the (A1) to I28, which is where I want to put the % Earned value. The problem is, it's the same cell as the function; which creates a problem.

Also, as indicated in my previous post, once it finds a value of 100%, I need it to stop, regardless of what month it is. So if the % Earned is 100% in L28 (May), that's it, I don't need it to look in June, July, etc.

 

[dleitch70]

Hi gaz_chops

Thanks for the correction. I was wondering why I couldn't get it to work. It works now...sort of. I entered it as an array, as you suggested. What I'm not sure about is the (A1). The function is as follows:

{=INDEX(L28:AH28,,MATCH(MONTH(I28),MONTH(K28:AG28),0))}

As you can see I changes the (A1) to I28, which is where I want to put the % Earned value. The problem is, it's the same cell as the function; which creates a problem.

Also, as indicated in my previous post, once it finds a value of 100%, I need it to stop, regardless of what month it is. So if the % Earned is 100% in L28 (May), that's it, I don't need it to look in June, July, etc.

 

[gaz_chops]

Sorry A1 is where I put a date to test it, it should be today()

Also, if you add min to the formula, it will return the % but won't report above 100%

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

Still entered as an array.

 

[dleitch70]

{=MIN(1,INDEX(L28:AH28,,MATCH(MONTH(TODAY()),MONTH(K28:AG28),0)))}

Doesn't like it! It come back with an #N/A - A value is not available to the formula or function

 

[gaz_chops]

Strange it works fine for me! Have you got dates across the whole range K28:AG28

 

[gaz_chops]

Hold on, how are your months entered across K28:AG28? Are they dates or literally just the months like Jan, Feb etc?

 

[jasonb75]

See if this works

=--TEXT(INDEX(C27:Y27,(MOD(MONTH(TODAY())+7,12)*2+1)),"0.00[<=1];\0[>1]")

 

[gaz_chops]

Or, if your months are entered as Jan, Feb etc

=MIN(1,INDEX(L28:AH28,,MATCH(TEXT(TODAY(),"mmm"),K28:AG28,0)))
No need to enter as an array

If they are entered as January etc then change the "mmm" to "mmmm"

 

[dleitch70]

Hi Jason,

Unfortunately it didn't work.

It's basically like this. There are two columns (B & C). B1 says Billed C1 says % Earned. This is repeated for D1 (Billed) & E1 (% Earned), then again for F1 (Billed) & G1 (% Earned). Basically every second column is % Earned. The 1st % Earned is for May, the 2nd is for June, 3rd July and so on.

In this scenario, it might be that the % Earned for May (column C) is 10 (cell C2). Skip a column, and the next % Earned (Column E) is 20 (cell E2) and so on...

Although there may be a value (forecast) in E2, if it's the month of May, I only want to grab the value from cell C2. If that value is 100, I don't need to go any further. If the value is not 100 and the month is now June, I want to grab the value from E2. if that is 100 I don't need to go any further. If it's not 100 and we now go into July, I need it to grab he % Earned for the July Column (G2) and so on. The value grabbed will be placed in Column A - so the value grabbed (10 or 20 in the example) will be placed in cell A2. This function will then be copied for each row below (3, 4, 5....).

I hope my explanation is clear.