Subtracting a single date value with an entire column of dates

[#1]

Hi All,

My requirement is to subtract a particular date with a column of dates i.e. submit_date in the load script and display the difference in days(networkdays included).

say that particular date to be say   d1=6/30/2015

I want to find the difference between this particular date and column of problem_submit_date with values say 4/13/2015,3/17/2015,3/12/2015 an so on.

I placed that date d1 into a variable in the load script named varMonthenddate,

To find the difference I am using the following formulae.

num#(Interval#( $(varMonthenddate) - Problem_Submit_Date,'dd'),'##,##0.##')

and assigning it a column name 'Monthend_Problem_interval'.

But the problem is that I am getting the column values as

-42184.2638

-42184.2567

-42181.7318

and so on which is not how require them.

I need them as difference in days.

Kindly Help.

Rahul.

[jonathandienst]

To get the diiference in days, just subtract the one date from the other.

$(varMonthenddate) - Problem_Submit_Date

If the variable value is a string, then you may need to interpret the date:

Date#($(varMonthenddate)) - Problem_Submit_Date

or

Date#($(varMonthenddate), '<your date format>') - Problem_Submit_Date

[#3]

Hi Jon,

Thanks for your reply.

I followed your steps to find the difference.

$(varMonthenddate) - Problem_Submit_Date: gives me the same output as I got earlier.

Also Why am I getting the difference in 42183.9908, 42181.17381 format can't understand

whereas using Date#($(varMonthenddate)) - Problem_Submit_Date did not give me any out put on the listbox.

Is there a way I can have it in Days.

Kindly suggest.

Rahul.

[sasiparupudi1 · Accepted solution]

Date#('$(varMonthenddate)','M/DD/YYYY')-date#(Dates,'M/DD/YYYY') as DateDiff

[MarcoWedel]

'$(varMonthenddate)' - Problem_Submit_Date

[#6]

Hi Marco,

Thanks for your reply,

But as with your formulae I am just getting the difference in 42183.9908, 42181.17381 format, whereas I want them in days.

Regards,

Rahul.

[MarcoWedel]

please post sample application

regards

Marco

[#8]

Hi Sasidhar,

Thanks your reply is giving me the solution I want.

It was generally taking the Date format as 'MM/DD/YYYY' which was the root cause.

I didn't need the rounded values so I removed the Date# function from the submit date.

Thanks any ways.

Regards.

Rahul