If critera is met, skip rows

[skaffapingvin]

Hello,

I wonder if this is possible?

I want to have a formula in column B, range B4:B1500.
Column C, range C4:C1500 contains random text or could be empty.
But if a cell in column C contains the word "PASSPORT" I want the cell in col B, 2 rows down to show todays date.

Example:
If C10="PASSPORT", then B12=Today(), IF false,""
How would this kind of formula look like?

 

[Joe4]

You would put this formula in cell B12:

=IF(C10="PASSPORT",TODAY(),"")

Just note that TODAY() will ALWAYS return the current date.
So tomorrow, any cell meeting that condition will show tomorrow's date, etc.

If you want to capture the current date, and hard-code it in the cell (like a Date Stamp), you would typically use VBA for something like that.
There are literally thousands of threads on this forum that deal with Date Stamps (the question is asked quite often).

 

[skaffapingvin]

Hi,

Thanks for replying. Actually it doesn't have to be todays date that is shown in col B, it can be any date such as 23-07-28 as an example.
So date stamp is not needed for this example.

=IF(C10="PASSPORT","23-07-28",""), would this be correct?

 

[Joe4]

What I am telling you is that the TODAY() function is dynamic, it will ALWAYS return the current date.

So if you had the formula:

=IF(C10="PASSPORT",TODAY(),"")

in cell B12, and C10 is equal to "PASSPORT", then cell B12 will return today's date.

If you save the file, and open it up again tomorrow, cell B12 will then show tomorrow's date (as it will always dynamically return the current date).

If that works for you, there is no issue.
If you are trying to capture the date that "PASSPORT" was entered into cell C10, and "freeze" that date so it doesn't change, then a formula approach will not work for you.

 

[skaffapingvin]

Yes I know that. Today() was perhaps a bad example, probably 23-07-28 is better

 

[Joe4]

=IF(C10="PASSPORT","23-07-28",""), would this be correct?

That will probably return a Text entry of "23-07-28", not a date value.

You can use this formula:

=IF(C10="PASSPORT",DATE(2023,7,28),"")

and apply a custom format on the cell of "yy-dd-mm".

 

[skaffapingvin]

Thanks, exactly what I was looking for

 

[Joe4]

You are welcome.
Glad I was able to help.