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
This topic has been closed for replies.
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.