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?

 

[dleitch70]

Hi gaz_chops.

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.

 

[gaz_chops]

I understand, what I don't know is how you have entered the Months?

Have you simply entered text as Jan, or text as January, or a date 1/1/2015?

 

[gaz_chops]

This is my test sample, it returns 60% for Jun

[TABLE="width: 1083"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][B][COLOR=#ff0000]60%[/COLOR][/B][/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]10%[/TD]
[TD]Feb[/TD]
[TD]20%[/TD]
[TD]Mar[/TD]
[TD]30%[/TD]
[TD]Apr[/TD]
[TD]40%[/TD]
[TD]May[/TD]
[TD]50%[/TD]
[TD]Jun[/TD]
[TD]60%[/TD]
[TD]Jul[/TD]
[TD]70%[/TD]
[TD]Aug[/TD]
[TD]80%[/TD]
[TD]Sep[/TD]
[TD]90%[/TD]
[TD]Oct[/TD]
[TD]100%[/TD]
[TD]Nov[/TD]
[TD]110%[/TD]
[TD]Dec[/TD]
[TD]120%[/TD]
[/TR]
</tbody>[/TABLE]

 

[dleitch70]

Hey, for each month there are 2 columns under it. For example: B1 and C1 are merged and say May. under that B2 says Billed, C2 says % Earned. under that I would have a value in B3 which is amount billed (i.e. $10,000). In C3 it shows the % we've earned (i.e. 10%). setup is exactly the same for June, July, August, etc.

So in your example:

Above Jan and 10 %, 2 cells are merged and say "May". Your Jan would say "Billed" and 10% would say "%Earned". Under Billed would be a dollar amount ($10,000), under %Earned would be 10.

 

[gaz_chops]

If you don't answer my question above, I can't help! Depending on how you entered the Month determines how we search for it in the formula.

 

[dleitch70]

Months are entered as May, June, July, August etc. (text)

 

[gaz_chops]

OK then try

=MIN(1,INDEX($C3:$Y3,,MATCH(TEXT(TODAY(),"mmmm"),$B$1:$X$1,0)))
(No need to enter as an array)

[TABLE="width: 1101"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]May[/TD]
[TD="colspan: 2"]June[/TD]
[TD="colspan: 2"]July[/TD]
[TD="colspan: 2"]August[/TD]
[TD="colspan: 2"]September[/TD]
[TD="colspan: 2"]October[/TD]
[TD="colspan: 2"]November[/TD]
[TD="colspan: 2"]December[/TD]
[TD="colspan: 2"]January[/TD]
[TD="colspan: 2"]February[/TD]
[TD="colspan: 2"]March[/TD]
[TD="colspan: 2"]April[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Billed[/TD]
[TD]% Earned[/TD]
[TD]Billed[/TD]
[TD]% Earned[/TD]
[TD]Billed[/TD]
[TD]% Earned[/TD]
[TD]Billed[/TD]
[TD]% Earned[/TD]
[TD]Billed[/TD]
[TD]% Earned[/TD]
[TD]Billed[/TD]
[TD]% Earned[/TD]
[TD]Billed[/TD]
[TD]% Earned[/TD]
[TD]Billed[/TD]
[TD]% Earned[/TD]
[TD]Billed[/TD]
[TD]% Earned[/TD]
[TD]Billed[/TD]
[TD]% Earned[/TD]
[TD]Billed[/TD]
[TD]% Earned[/TD]
[TD]Billed[/TD]
[TD]% Earned[/TD]
[/TR]
[TR]
[TD]20%[/TD]
[TD] 1.00[/TD]
[TD]10%[/TD]
[TD] 1.00[/TD]
[TD]20%[/TD]
[TD] 1.00[/TD]
[TD]30%[/TD]
[TD] 1.00[/TD]
[TD]40%[/TD]
[TD] 1.00[/TD]
[TD]50%[/TD]
[TD] 1.00[/TD]
[TD]60%[/TD]
[TD] 1.00[/TD]
[TD]70%[/TD]
[TD] 1.00[/TD]
[TD]80%[/TD]
[TD] 1.00[/TD]
[TD]90%[/TD]
[TD] 1.00[/TD]
[TD]100%[/TD]
[TD] 1.00[/TD]
[TD]110%[/TD]
[TD] 1.00[/TD]
[TD]120%[/TD]
[/TR]
</tbody>[/TABLE]

 

[jasonb75]

Hi Jason,

Unfortunately it didn't work.

I think I may have misread one of your posts, see if this works

=MIN(INDEX(C2:Y2,(MOD(MONTH(TODAY())+7,12)*2+1)),1)

 

[dleitch70]

Good morning gaz.

Almost got it. Your function works...thanks! The only problem with it now is that if the % Earned field for current month (i.e. June) is empty, the function returns a value of 100%. In my case (and in your example) 10% may be Earned in May, nothing is earned in June or July, but in August we may now be at a % Earned of 50. So from May-July I need the % Earned to remain at 10%, even though June and July will be empty. When We hit August the value of 50% would be picked up.

I could l go May 10%, June 10%, July 10%, August 50% but some will confuse that and think that by June we earned 20%, by July 30% and by August 80%. This is why I need to leave the cells blank if nothing was billed for that month.

I hope that makes sense.

 

[dleitch70]

Hi Jason,

The function works...thanks! The problem is that if there is value for the previous month (May) and it's 100%, the function doesn't pick it up.

In my case 10% may be Earned in May, nothing is earned in June or July, but in August we may now be at a % Earned of 50. So from May-July I need the % Earned to remain at 10%, even though June and July will be empty. When We hit August the value of 50% would be picked up.

The opposite of this is that if 100% is earned in any month prior to current month, the function doesn't need to go any further. We hit 100% and that's it. Current month, and all subsequent months will be empty.


Basically I need it to And if the value for current month is empty (nothing entered) the function defaults to 100%