Multiple blanks required

[kellyfamily]

Apologies if this has been covered but i couldn't the answer i was looking for.

I have a spreadsheet with the following headings

Effective Date

Expiry Date

Status

Date served

<tbody>
</tbody>

I am currently using the following formula under the Status heading: =IF(K21="",IF(I21<TODAY(),"UNSERVED","Pending"),"Served"), if i drag the formula down the column and do not have an expiry date status is unserved in the blank cells, how can i fix it so it remains blank until data is entered in the expiry date column, i also would like Unserved, pending and served to be different colours.

Thanks

 

[Joe4]

Welcome to the Board!

It looks like your formulas are not coming across fully.
See this here: https://www.mrexcel.com/forum/about-board/680917-formulas-not-appearing-completely-read.html

 

[kellyfamily]

=IF(K38="",IF(I38 < TODAY(),"UNSERVED","Pending"),"Served")
is the formula im using, i want the status column to remain blank
until the expiry date has been filled with the status changing
depending if it is served or unserved

 

[kellyfamily]

When a date is entered in the served date column or when the document has passed
the expiry date.

And thanks for this forum, i have found some really great tips

 

[Michael M]

Maybe this

=IF(K38<>"","Served",IF(I38 < TODAY(),"UNSERVED","Pending"))

 

[kellyfamily]

Thanks for your help, but the status column still displays unserved when all other
columns are blank

 

[Joe4]

I think it still isn't quite clear what you are trying to do.
Can you show us some sample data (different scenarios) along with what your expected outcome is in each of them?
Be sure to clarify which columns are I and K.

 

[kellyfamily]

OK. My spreadsheet has multiple tables, but i would like these four to work together.
The first column is the effective date. (Remains blank until data is entered)
Second column is expiry date (remains blank until data is entered)
Third column is the status. If the second column has data this should say Pending, cell should be blue.
Fourth column is the date served, if a date is entered, third column is to read Served, cell should be green.
If no date is entered in column four and the expiry date has passed third column should read Unserved, cell should be green.
Please tell me if im asking to much and i will re-assess my spreadsheet