Count number of days and formula with text result

**joseph_yap22** · 05-09-2012, 03:22 PM

Hi to all wizards,

Please help me to automate the attached sheet. Really im poor about excel knowledge.

In column C (DURATION), I want a formula to calculate the days from the date given in column B (ATA) that will result like (8 DAYS)

In culomn E (REMARKS), I want a formula that will result to text like if culomn C (DURATION) is less than 20 days the result is UNDER PROCESS, if column C (DURATION) is more than 20 days the result is FOLLOW UP, if column C (DURATION) is more than 30 days the result is DELAYED and if any date is encoded in column D (RECEIVED DATE) the result is RECEIVED.

I want also to fill the color the ranges from A to E depending on the column E (REMARKS) same as in the attachment.

Im really sorry. I need your help.

Thank you and God bless you always!

**Paul** · 05-09-2012, 03:45 PM

Hi Joseph,

In the future, please only post one question per thread. The second and third questions have nothing to do with the thread title.

For column C, in cell C5 simply use:

=TODAY()-B5

Fill that down to C8.

In E5 use the formula:

=IF(ISNUMBER(D5),"RECEIVED",LOOKUP(C5,{0,21,31},{"UNDER PROCESS","FOLLOW UP","DELAYED"}))

Fill that down to E8.

You can then use conditional formatting to highlight the various remarks in column E. (Search for "Conditional Formatting" using the search function above and you'll see many examples.)

**FDibbins** · 05-09-2012, 03:48 PM

Try the attached and let me know if it gives you what you are after?

**joseph_yap22** · 05-09-2012, 05:18 PM

Thank you so much Sir!

Sorry and Yes I'll do it in the future.

Appreciated.

**Haseeb Avarakkan** · 05-09-2012, 07:19 PM

Joseph, don't know is this matte for you. Since you are using TODAY() formula in column C, this will keep count the duration, even if remarks changed to 'Received'.

If received date always be > ATA date, then you can use IF formula to check, if D=date then D-C else TODAY()-B.
If received date <>ATA then I think will require Change_Event VBA.