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.

[TABLE="width: 471"]
<colgroup><col><col span="4"><col></colgroup><tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Date 1[/TD]
[TD]Date 2[/TD]
[TD]Date 3[/TD]
[TD]Date 3[/TD]
[TD]Expected Result[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]03/07/2019[/TD]
[TD]02/07/2019[/TD]
[TD]01/07/2019[/TD]
[TD]27/06/2019[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]Rahul[/TD]
[TD]22/06/2019[/TD]
[TD]02/07/2019[/TD]
[TD]01/07/2019[/TD]
[TD]27/06/2019[/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD]Silas[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]02/07/2019[/TD]
[TD] [/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]02/07/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD]Jones[/TD]
[TD]02/07/2019[/TD]
[TD]01/07/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD]Prem[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]03/07/2019[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]Ram[/TD]
[TD]03/07/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]Rajesh[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]03/07/2019[/TD]
[TD] [/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]Bruce[/TD]
[TD] [/TD]
[TD]03/07/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]Vince[/TD]
[TD] [/TD]
[TD]03/07/2019[/TD]
[TD]03/07/2019[/TD]
[TD] [/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]Jacob[/TD]
[TD]03/07/2019[/TD]
[TD]03/07/2019[/TD]
[TD]03/07/2019[/TD]
[TD]03/07/2019[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]Thomas[/TD]
[TD]03/07/2019[/TD]
[TD]03/07/2019[/TD]
[TD]03/07/2019[/TD]
[TD] [/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]Gill[/TD]
[TD]03/07/2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]Joy[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Pending[/TD]
[/TR]
</tbody>[/TABLE]

 

[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?