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.
Formula to show number of days since last contact, from another column
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’)
+16As 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.
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’)
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.
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.
Thanks very much Vernon. I will keep this in mind for future efforts.
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.
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.
+16The “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.
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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.