3 Replies Latest reply on Nov 19, 2014 11:55 AM by Mark Holtz

# Difference between reference date and shipment date

I need to get the difference between reference date and shipment date. My reference date is the end date of time name 'Launch to date'. Can some one please suggest how this could be done ?

Attached are the input,desiredoutput excel and twbx file.

• ###### 1. Re: Difference between reference date and shipment date

Hi Amit,

This can be achieved using the below formula

Time elapsed in days:

if [Time Name] = "Launch to Date"

then

datediff("day",[Shiepment Date],[End Date])

end

:

Please find the sample workbook attached

Hope this helps

Thanks,

Praveen

• ###### 2. Re: Difference between reference date and shipment date

That will only work if I am doing calculations for Launch to Date. I need to calculate for all the time periods keeping the reference date to the end date of launch to date.

• ###### 3. Re: Difference between reference date and shipment date

Hi Amit,

praveen's solution gets you part of the way there. We just need to make the End Date dynamic.

You can create a parameter to allow the user to select the Time Name (from a list).

Then create a calculated field for EndDate as:

IF [Time Name] = 'Launch to Date' THEN TODAY()

ELSEIF [Time Name] = 'Current Year' THEN DATE('12/31/'+STR(YEAR(TODAY())) )

ELSEIF [Time Name] = 'Current Month' THEN DATETRUNC('month',DATEADD('month',1,TODAY())) - 1

END

Now you can use EndDate in the DATEDIFF function praveen supplied.

Attached is an example.

Cheers