If Formula

[rentonhighlands]

Please write a formula
I have two columns, estimated Date and Delivery date.
If a date is today or from the past then copy that date into the Delivery date column and remove it from the estimated date column.

Carrier Estimated Delivery Date	Delivery Date
12/31/2024
12/31/2024
12/5/2024

 

[Joe4]

A cell can either have a hard-coded value or a formula in it, but never both at the same time.
So you are not going to be able to "blank out/remove" a hard-coded value from a cell with a formula.
To do something like that would probably require VBA.

Are you open to a VBA solution?
If so, please let us know the exact ranges these date occur in, so we can write the code accordingly.

 

[rentonhighlands]

not open to VBA, maybe I put all the dates in 1 column?

 

[Cubist]

What's the reason for removing the date in the Estimated column? Formula logic, you could use the Delivery column if not blank. Need more context on what you're trying to accomplish.

 

[rentonhighlands]

I need to report what is estimated to be delivered and delivered.

 

[Joe4]

Why do you need the Estimated Date to be cleared out after the other column has a date in it?
In your reporting formulas, you should be able to use logic looking at both those columns to get whatever results you need.

If your provide more details of your reporting formulas, we can probably help you update them to get what you want.

 

[rentonhighlands]

I am reporting per tier (count) what is estimated delivery, and delivered. I pull vlookup from project sheets to pull in dates. I can't have dates in both columns then the count will be wrong.

Tier	Carrier Estimated Delivery Date	Delivery Date
	1/31/2025
	5/31/2025
	5/31/2025

 

[Joe4]

I can't have dates in both columns then the count will be wrong.

Sure you can, you just have to adjust your count formula (which is what I am getting at).
You can use a COUNTIFS formula to count how many records have dates in both columns versus how many have dates in just one column.

 

[Joe4]

For example, see here:

Formula for cell B2 (and copy down to B4):

=IF(A2<=TODAY(),A2,"")

Formula in cell B6:

=COUNTIF(B2:B4,">0")

Formula in cell B7:

=COUNTIFS(A2:A4,">0",B2:B4,"")