IF MAX formula using dates

[KC]

=IF($F4="On Board",MAX(0,((S$1-$M4)/7))*Q4,"")

S1= 3/31/2010
M4= 10/15/2009
Q4= $573

How can I fix the formula above so it will correctly calculate the cost
based on a 2009 date, or a 2010 date. Currently, the formula only works if
M4 has a date of 2010.

 

[jlclyde]

=IF($F4="On Board",MAX(0,((S$1-$M4)/7))*Q4,"")

S1= 3/31/2010
M4= 10/15/2009
Q4= $573

How can I fix the formula above so it will correctly calculate the cost
based on a 2009 date, or a 2010 date.  Currently, the formula only works if
M4 has a date of 2010.

This is not what you want. I am not sure what that is but this
formula says that if F4 = On Board then find the max of 0 or the
difference between 2 dates / 7 * Q7. If it does not = On Board then
return a blank cell. More data woudl be helpful.
Jay

 

[KC]

Hi Jay, the info below is in the cells for the formula provided. I am trying
to say, if F4 = On board, then calculate the amount of $'s it will cost in
1Q2010 at $573 per week. S1 will always say 3/31/2010, M4 might be any date
in 2009 or 2010. and Q4 might be any amount.

S1= 3/31/2010
M4= 10/15/2009
Q4= $573