Calculate Days from Today to a Projected Open and Projected Close Dates

[hellyars]

DateDif question....

I want to calculate how many days are left between

1. Today() and a projected OpenDate
2. Today() and a projected Close Date
3. If Today() is past the Projected Open Date I want to return a 0.  Null will not work as there are a large number of records for which there are no dates.

Sample Dates...

RecordID	Open	Close
1	2021-10-01	2021-11-30
2	2021-08-12	2021-09-15
3	2021-12-01	2022-01-31
4
5	2022-02-01	2022-06-15

[mbarone]

"0" is not a valid date format.  So you cannot have "0" in a date field.  Maybe pick a ridiculous date like 12/31/4712.  That will serve as your "0".

[hellyars]

@mbarone  My bad.  But, you still get the point.  Then any value with a negative date or something that allows me to distinguish a record that is past a due date from those that have no dates.

[mbarone]

Yes, or stick with the nulls and add a field called "past_due" with values of 1 or 0, where 1 would mean a negative date diff.