Validation in 4 columns for todays date

[exceluser9]

Hi Team,

I have below data, i require a formula in column E where it should say pending / completed.

Wherever there is no todays date it should say pending and where todays date is present it should say completed.

Dates ate populated in this sheet by a vlookup from different sheet.

Column A	Column B	Column C	Column D	Column E	Column E
Name	Date 1	Date 2	Date 3	Date 3	Expected Result
Jack	03/07/2019	02/07/2019	01/07/2019	27/06/2019	Completed
Rahul	22/06/2019	02/07/2019	01/07/2019	27/06/2019	Pending
Silas			02/07/2019		Pending
James	02/07/2019				Pending
Jones	02/07/2019	01/07/2019			Pending
Prem				03/07/2019	Completed
Ram	03/07/2019				Completed
Rajesh			03/07/2019		Completed
Bruce		03/07/2019			Completed
Vince		03/07/2019	03/07/2019		Completed
Jacob	03/07/2019	03/07/2019	03/07/2019	03/07/2019	Completed
Thomas	03/07/2019	03/07/2019	03/07/2019		Completed
Gill	03/07/2019				Completed
Joy					Pending

<colgroup><col><col span="4"><col></colgroup><tbody>
</tbody>

 

[tyija1995]

Re: Validation in 4 columns for todays date - Urgent

Hey try this:

=IF(OR(B2=TODAY(),C2=TODAY(),D2=TODAY(),E2=TODAY()),"Completed","Pending")

 

[Fluff]

Re: Validation in 4 columns for todays date - Urgent

Another option
=IF(ISNUMBER(MATCH(TODAY(),B2:E2,0)),"Completed","Pending")

 

[exceluser9]

Re: Validation in 4 columns for todays date - Urgent

Hi,

Formula is not working, to make it work i have to paste special the date and click on the cell (F2) and then hit tab. Post which im getting results. Any idea to fix this?

And i require one more formula where even if date is any other date in column E it should say completed. Except the date 00/01/1900

 

[Fluff]

Re: Validation in 4 columns for todays date - Urgent

Who are you talking to?
There are two replies.

 

[exceluser9]

Re: Validation in 4 columns for todays date - Urgent

Hi,

Both arent working

 

[Fluff]

Re: Validation in 4 columns for todays date - Urgent

Are your dates proper dates?
If you format the dates cells to General do you see numbers like 43649?