Since formula columns are only calculated upon request or change, it's important to note that new Date() will only ever update when other data in the box is changed or when a pipeline refresh is triggered, which can lead to inconsistent data in your pipeline.
Finding the difference between a Date column and today
If you only need to find the difference between two dates (e.g. the date a deal closed and today), we recommend using one of the two options in Format to calculate the difference:
# of Days Since – this will give you a positive exact number of days since the provided date. If the date is in the future, this will give you a negative number.
# of Days Until – this will give you a positive exact number of days until the provided date. If the date is in the past, this will give you a negative number.
If you need this data in a separate column from the original, you can also create a new Formula column based on the original column (
=$'Column name') and format it according to your business need.