count of metrics per period

[DM89]

Hi,

I have read multiple posts about similar issues, but it didn't help me to solve it.

My issue is

I want to count the number of new and existing contacts. New contacts are updated in the last 30 days. Existing contacts are updated more than 30 days ago. My date field is 'lastUpdated new vs existing' and uses the format 'dd/MM/yyyy'.

What I have tried

=inday ([lastUpdated new vs existing], Now(), -30) I get numbers instead of boolean.

and

=if([lastUpdated new vs existing]<Today()-30,'new','old') Every contact gets the status 'new'.

Hopefully someone can help me and knows the solution. If more information is needed, please let me know.

Kind regards,

Dennis

[jcmachado]

One way to approach this problem is to use the DATEDIF function in combination with IF. The DATEDIF function calculates the difference between two dates in a specified unit (e.g. days, months, years). The syntax is: DATEDIF(start_date, end_date, unit).

The IF function can be used to check if the difference between the "lastUpdated new vs existing" field and today's date is less than or equal to 30. If it is, the contact is considered new, otherwise it is considered existing.

Here is an example formula you can use:

=IF(DATEDIF(A2,TODAY(),"D")<=30,"New","Existing")

Where A2 is the cell containing 'lastUpdated new vs existing' field.

You can use this formula in a new column next to the 'lastUpdated new vs existing' column and copy it down for all the contacts.

[DM89]

Hi,

Thanks for your reply. I can't select the Datedif fuction. Is there a substitution for this function?