I have a dddd format but the value is still a number.

[furstukin]

I have a spreadsheet I am working on where I have to forecast based on a Fiscal Month and use daily percentages for each day i.e. Monday Tuesday Wednesday etc.

Currently in say cell A2 I have the date and right above that in cell A1 is a reference to A2 but with a dddd format rather than the d/m/yy format.

What I want to do is have yet another cell, say A3, reference A1 with a nested "IF" saying something like.

=if(A1=Monday,A5,if(A1=Tuesday,B5,if(A1=Wednesday,C5,Null))).... etc so that it will pick the correct percentage no matter what day of the week my fiscal month starts on.

However it always sees the cell A1 as a number value rather than the day of the week it is displaying so it returns #VALUE! each time I try this.

Is there anyway to get it to read the day of the week rather than a value for the date of the month it represents?

 

[VoG]

Try instead in A1

=TEXT(A2,"dddd")

 

[MrKowz]

Try replacing your IF formula with:

=INDEX(A5:G5,,WEEKDAY(A1,2))

 

[furstukin]

Try instead in A1

=TEXT(A2,"dddd")

I could not seem to make that work for me. It just kept saying #Name?


<HR style="BACKGROUND-COLOR: #ffffff; COLOR: #ffffff" SIZE=1>

Try replacing your IF formula with:

=INDEX(A5:G5,,WEEKDAY(A1,2))

However this worked perfectly and with out the super long nested "if" statement. Thanks for the help everyone.