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?

 

[jasonb75]

That requires a whole different approach.

This one is based on Gaz's last suggestion.

=MIN(1,MAX(IF(INDEX(MOD(COLUMN($C3:INDEX($C3:$Y3,MATCH(TEXT(TODAY(),"mmmm"),$B$1:$X$1,0))),2),0),$C3:INDEX($C3:$Y3,MATCH(TEXT(TODAY(),"mmmm"),$B$1:$X$1,0)))))

It is helpful if you give us details like the presence of blank cells before we start. Blanks and merged cells are 2 of the easiest ways to make a good formula fail.

 

[gaz_chops]

That requires a whole different approach.

This one is based on Gaz's last suggestion.

=MIN(1,MAX(IF(INDEX(MOD(COLUMN($C3:INDEX($C3:$Y3,MATCH(TEXT(TODAY(),"mmmm"),$B$1:$X$1,0))),2),0),$C3:INDEX($C3:$Y3,MATCH(TEXT(TODAY(),"mmmm"),$B$1:$X$1,0)))))

It is helpful if you give us details like the presence of blank cells before we start. Blanks and merged cells are 2 of the easiest ways to make a good formula fail.

Note you need to enter Jason's formula as an array.

Also, as Jason says the more details you give at the start the more chance of us getting it right sooner.
You might want to take a look at the footnote in my post, it gives a number of options for you to post a sample of your data, which if you need to post again is far more helpful.

 

[jasonb75]

Note you need to enter Jason's formula as an array.

Try evaluating the formula without entering it as an array.

The first INDEX() function is there to eliminate the need for C-S-E confirmation

 

[gaz_chops]

Try evaluating the formula without entering it as an array.

The first INDEX() function is there to eliminate the need for C-S-E confirmation

I thought not at first, but it doesn't appear to work correctly unless CSE?
Without CSE

[TABLE="width: 478"]
<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]
[/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]
[/TR]
[TR]
[TD="align: right"]100%[/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]
[/TR]
</tbody>[/TABLE]

With CSE

[TABLE="width: 478"]
<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]
[/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]
[/TR]
[TR]
[TD="align: right"]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]
[/TR]
</tbody>[/TABLE]

It appears to return 100% regardless if not entered as CSE.

 

[jasonb75]

Good catch Gaz, I evaluated it twice before posting and still missed it.

As one of the functions was a failed attempt to eliminate the need for cse, the formula can be shortened slightly.

=MIN(1,MAX(IF(MOD(COLUMN($C3:INDEX($C3:$Y3,MATCH(TEXT(TODAY(),"mmmm"),$B$1:$X$1,0))),2),$C3:INDEX($C3:$Y3,MATCH(TEXT(TODAY(),"mmmm"),$B$1:$X$1,0)))))

Obviously confirmed as an array with CSE.

 

[dleitch70]

Thanks gents!