Subtract dates into a column

[alec1982]

Hi,

I have two date columns coming from excel file

Starting date and End Date with the format MM/DD/YYYY

I am building a table chart and would like to have a period column

Anybody knows how could I create a a new column to calculate the period between Start and End date and put it in percentage % of completing the period?

In other words, I would like to get the period in days and then get the percentage of completion of the period on today

Thxs,

[chematos]

To get the difference of two dates you only need to use EndDate - StartDate.

So you can calculate both range of days and make the division to get he percentage.

If you don´t get it, please, expose your problem with a complete example.

Regards.

[alec1982]

Hi,

Example: Starting Date 07/09/2010

               End Date      12/25/2016

I am looking to build a column to show me the period in total days.

and then another colmun to calculate the current date - starting date in total days.

Thanks for your help

[chematos]

You're welcome.

Could you tell me if it works for you doing EndDate - StartDate and Today() - StartDate ???

Take care with Today() function, normally, if you set the date format like:

SET DateFormat='MM/DD/YYYY'; today function returns that format so you could do the substract without problems but if you want, you could use Date(Today(),'MM/DD/YYYY') to be sure that is returned the same type data.

Regards

[Anonymous]

Subtracting any 2 dates will give you the number of days between. It is probably safest to use the num() function on the dates to avoid possible syntax errors. For example:
num([End Date]) - num([Start Date]) will give you the entire period.
You will probably want to set a variable with the value of today() as follows in your load script:
LET v_Today = today();
Then you can calculate the difference between today and start as follows:
num(v_Today) - num([Start Date])

-Phil