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

Question

DateDif question....

I want to calculate how many days are left between

* Today() and a projected OpenDate
* Today() and a projected Close Date
* 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

OpenClose12021-10-012021-11-3022021-08-122021-09-1532021-12-012022-01-314  52022-02-012022-06-15

mbarone · Answer

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.

hellyars · Answer

@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 · Answer

"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".