IF STATEMENT - continuing dates

[dunmore83]

Hi,

I would really appreciate some help with the following:

- I have a string of dates in date format 14-Aug-11 in cells B5:AG5

I only want the dates to continute on this ROW if the values in a corresponding cell equal YES. If the value in this cell is NO i would like the cell with the date to return N/A

For example R11 is NO so I want the value in T5 (the date cell) to be "N/A". Otherwise if R11 was YES I want the date in T5 to show & so on.

Any more clarification, please let me know.

THANKS FOR YOUR HELP!!!

Cheers

 

[jbeaucaire]

Since you didn't show your date formula in T5, the pseudo formula would be in T5:

=IF($R$11="NO", "N/A", yourdateformulahere)

 

[dunmore83]

Hi thanks for your response.

My date formula is [$-409]d-mmm-yy;@

Writing it like this doesnt seem be valid

=IF($R$11="NO", "N/A", [$-409]d-mmm-yy;@)


Your comments much appreciated.

Cheers

 

[jbeaucaire]

That's not a date formula, that's a date FORMAT applied to a cell. What logic is being used to decide WHAT date goes in this cell?

 

[dunmore83]

Thanks.

So if:

- cell R11 is NO then I want cell T5 to display N/A

- cell R11 is YES then I want cell T5 to display the date from cell Q5 plus one Day (i.e. 20-Jun-11 to 21-Jun-11).

With the exception of Friday to the next day. If it's Friday & the next cell trigfgers with a YES value then I want the next date cell to be plus 3 days (i.e Friday to Monday)

Really appreciate your assistance. Cheers

 

[jbeaucaire]

Like so:

=IF($R$11="NO", "N/A", Q5+1+(WEEKDAY(Q5)=6)*2)

 

[dunmore83]

Thanks that works great!!!

I have one more variable to add to this equation:

- Cell R11 can display 3 variables: YES, NO & N/A

I only want the formula in T5 to display the date if R11 is YES. If R11 is either NO or N/A I want T5 to dislpay N/A

Thanks in advance. Cheers.

 

[jbeaucaire]

Try:

=IF($R$11="YES", Q5+1+(WEEKDAY(Q5)=6)*2, "N/A")

 

[dunmore83]

THANKS SO MUCH. YOU ARE AWESOME!!

One more question in this area:

- I need cell H4 to add one day to cell E4, but if cell E4 is a Friday then cell H4 needs to add 3 days i.e. be mondays date

No other criteria on this one. Simply as above

(cell H4 & E4 are just in date format i.e. 14-Aug-11)

Thanks!!

 

[dunmore83]

just another quick question;

the formula you provided returns the date without the year i.e. 14-Aug

- is there a way to return it including the year?? i.e. 14-Aug-11

Also anyway to return the date in Capitals?? i.e. 14-AUG-11

Thanks!!!