Formula to show number of days since last contact, from another column

[Amy_Zalman]

We are keeping a list of open leads that has a column for “date of last contact.” I have been using the sort function to remind which leads may need recontacting. But I’d like to create a column that produces the number of days since the date of last contact. It looks as if the DATETIME_DIFF formula is the idea I am looking for but I am not sure how to actually use it if one of the dates is pulled from a column. Grateful for any ways to generate this column, whether with this formula or any other.

[Christoff]

I have a feeling you might have already figured this out (you were so close) but, if not, take a look at DATETIME_DIFF in the Date and Time Functions.

The format is: DATETIME_DIFF([date1], [date2], ‘units’)
[date1] and [date2] can be a Date/Time field in your table or a Date/Time function like TODAY() .

So, create a new field with type “Formula” and call it something like “Numbers of days since date of last contact” and enter this formula: DATETIME_DIFF(TODAY(), {Date of Last Contact}, ‘d’)

[Vernon_Fowler]

As well as DATETIME_DIFF described by @Christoff, there are the TONOW([date]) and FROMNOW([date]) functions which provide the number of days between your [date] and now.

[Amy_Zalman]

Thanks for your confidence in me—I did not figure that out on my own! In fact I don’t quite understand the function of the ‘d’ in your formula. But it works perfectly. Thank you for your help.

[Amy_Zalman]

Thanks very much Vernon. I will keep this in mind for future efforts.

[Christoff]

The “d” is one of the unit specifiers for the DATETIME_DIFF function and it refers to number of days.

@Vernon_Fowler’s suggestion of the TONOW function is better when you want the number of days from a specific date to today: One variable instead of two, and no need for a unit specifier.

[Vernon_Fowler]

I’ve just learned that TONOW function uses variable units. So far I’ve seen it show:

• 5 hours
• 13 days
• 2 months

This may or may not be desired, so consider this in choosing between DATETIME_DIFF and TONOW functions too.