14 Replies Latest reply: Jun 16, 2014 7:05 AM by Jagan Nalla

# calculating date difference

hi

i am trying to calculate the  difference from the last modified date to present date..(today) i have attach an image of the data table that i am using.. as an output i need to take a count of all the incidents that is in the "pending " state more than 10 days.. can some one plz help me with this really really would appreciate the help

• ###### Re: calculating date difference

You can create a flag in your script and use SUM(Flag)

IF(Status = 'Pending' and Interval(Today() - [Modified Date],'DD') >= 10, 1, 0) as Flag

• ###### Re: calculating date difference

hi manish

my today date n the last modified date formats r different so will this function work??

• ###### Re: calculating date difference

=if((today()-LastModifiedDate)>10 and (status = 'pending'),1,0)

• ###### Re: calculating date difference

hi sneha

my today date n the last modified date formats r different so will this function work??

• ###### Re: Re: calculating date difference

convert both dates to same format for comparision purpose

=if((timestamp(today())-timestamp(LastModifiedDate)>10  and status = pending),1,0)

• ###### Re: calculating date difference

Try to convert your date column into date format and then use the expression.

• ###### Re: calculating date difference

i converted my date column in to the right format but i am still unable to get a count :/

• ###### Re: calculating date difference

Can you attach sample file.

• ###### Re: calculating date difference

i am sorry Jagan i can not attach the file due to official reasons but i have attached an image of the data table in that i have converted my last modified date in to the proper format... i need to take a count of all the incidents which is in the in progress state more than 10 days..

=if((today()-SMod_date)>10

this function might be right but how can i take a count ?? :/

• ###### Re: calculating date difference

try this..

Count(IF((today()-SMod_date)>10 and status='pending',IncidentID)

• ###### Re: calculating date difference

Can you provide your sample data in excel file?

• ###### Re: calculating date difference

count( distinct if(Status='In Progress' and interval(today(),S_Mod_Date,'DD')>10,[Incident ID])

hope this helps

• ###### Re: calculating date difference

=Count(If(Interval(Today()-S_Mod_Date,'D')>10 and Status='In Progress',IncidentID,Null()))

• ###### Re: calculating date difference

In the backend create a new field if((Today() - SMod_Date)>10,1,0) AS NoOfDays

Use

SUM({\$<status={'pending'}>}NoOfDays)