Validation in 4 columns for todays date

exceluser9

Active Member
Joined
Jun 27, 2015
Messages
362
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 AColumn BColumn CColumn DColumn EColumn E
NameDate 1Date 2Date 3Date 3Expected Result
Jack03/07/201902/07/201901/07/201927/06/2019Completed
Rahul22/06/201902/07/201901/07/201927/06/2019Pending
Silas 02/07/2019 Pending
James02/07/2019 Pending
Jones02/07/201901/07/2019 Pending
Prem 03/07/2019Completed
Ram03/07/2019 Completed
Rajesh 03/07/2019 Completed
Bruce 03/07/2019 Completed
Vince 03/07/201903/07/2019 Completed
Jacob03/07/201903/07/201903/07/201903/07/2019Completed
Thomas03/07/201903/07/201903/07/2019 Completed
Gill03/07/2019 Completed
Joy Pending

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

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
747
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,145
Office Version
365
Platform
Windows
Re: Validation in 4 columns for todays date - Urgent

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

exceluser9

Active Member
Joined
Jun 27, 2015
Messages
362
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,145
Office Version
365
Platform
Windows
Re: Validation in 4 columns for todays date - Urgent

Who are you talking to?
There are two replies.
 

exceluser9

Active Member
Joined
Jun 27, 2015
Messages
362
Re: Validation in 4 columns for todays date - Urgent

Hi,

Both arent working
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,145
Office Version
365
Platform
Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,090,459
Messages
5,414,647
Members
403,540
Latest member
mmorejon1215

This Week's Hot Topics

Top