Formula to add 21 days to a date and take into account any extensions

[Zeus82]

Hello my Excel Angels,

I know this will be very basic to you guys however I am stuck with a formula.

When a letter is sent out the receiver has 21 days to respond however on occasion and extension will be granted.

I was looking for a formula which I could enter into column G that will add 21 days to the date entered into column E however if there is a figure entered into the same row in Column F then this will be taken into account.

I think I would like to have the entire row highlight in red if this date is exceeded however I think I can do this will a conditional Formatting Rule.

Please see an example of my work book attached.

Thanks in advance.

Zeus82

[etaf]

=E5+21+F5
does that work for adding 21 days plus whatever the extension is

If no name added to the sheet
then you could use
=IF(B5="","",E5+28+F5)
to avoid 1900 dates appearing

for conditional formatting
Select the table range
and use as a formula
=AND($G5 <> "",$G5 < TODAY())

[modytrane]

Formula in G5, copy it down.

You need ISBLANK to avoid getting incorrect dates when column E is blank.

[Zeus82]

Hi Etaf & Modytrane,

I very much appreciate your help with this.

I was able to get the very basic formula of =G5+21+E5 however if no value in E5 then this wouldnt work and when copying the formula down F column the cells weren't blank.

Both of the formulas that you have provided work great so thank you so much for your help.

The formula for conditional formating is great, only think is though that I guess i will need to incorporate an entry where I can enter complete and have the formula take this into account otherwise the rows will remain red for ever. Do you know how I could do this?

Thanks again.

Zeus82

[etaf]

where are you putting "Complete"
=AND($G5 <> "",$G5 < TODAY())
you could add a condition
=AND($G5 <> "",$G5 < TODAY(), $Cell <> "complete")
so Cell is the cell reference you intend to put the word complete into

=IF(B5="","",E5+21+F5)
you could change to
=IF(E5="","", E5+21+F5)

[Zeus82]

Hi Etaf,

Thank you so much!! That worked perfectly - The spreadsheet now does what I was looking for it to do

[etaf]

you are welcome

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.