• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Comparing dates column with given date and writing the results in new column

dvm49

New Member
I have the data like the attached excel sheet. And I wanted to compare column B, C, D, E & F with the input date '2020-09-25 12:00:00 AM' and if the date in column B,C,D,E & F is greater than the input date, I wanted leave them blank without any message if not I wanted to return "Offer Expired".
 

Attachments

dvm49

New Member
Hello p45cal,

Thanks for helping. I hope you have performed the comparison against all columns which is correct. But the result should be in only one column. Which means, we are comparing the dates in B,C,D,E&F with the input date '2020-09-25 12:00:00 AM' and if any one of the date in the column (B,C,D,E,F) is greater than the input date then we can start writing the results in the new column. So the rule should be pick up the greatest date from the column (B,C,D,E,F) against the input date and then write the results.

08:21:59​
 

p45cal

Well-Known Member
In cell H2 of the last file I attached:
=IF(COUNTBLANK($B2:$F2)=5,"", IF(MAX($B2:$F2)>$H$1,"","Offer Expired"))
or:
=IF(COUNTBLANK($B2:$F2)=5,"", IF(MAX($B2:$F2)<$H$1,"","Offer Expired"))
because I'm not sure which way you want it.
Copy down.
 

dvm49

New Member
Thanks it helped :)

By the way would like to know, is there a shortcut in applying the formulae in the cell? I have the formula in H2 and then dragged it down using the cursor. Was wondering if there are any other easy steps.
 

p45cal

Well-Known Member
Make the table into a proper Excel Table, which:
1. When you edit the top formula, if all the formulae in that column are the same (or there are no formulae) the whole column of the table will be filled with the same.
2. When you add or delete rows to the table, formulae will be added/deleted automatically.
3. You can use a table's structured formulae instead of cell references.

See attached.
 

Attachments

Last edited:
Top