Subtract ProjectStartDate from current date/time

# Subtract ProjectStartDate from current date/time

• Tuesday, October 18, 2011 1:48 PM

Hello all,

I’m trying to create a simple SSRS report in Visual Studio 2008 that will show a stoplight on whether a schedule is ahead or behind where it is scheduled to be "today" based on % work complete.  I currently use a custom field in Project Pro 2010 that calculates a stoplight for each individual task based on the duration and it works perfectly but want to move it into SSRS with the % work.  I'm running into a problem with a formula where I need to subtract the ProjectStartDate.Value from now() and I'm getting a "Operator '-' is not defined for types 'Date' and 'Object'."  Anyone have any suggestions on how I can alter this formula to get it to work?

Formula that works as a custom field in Project Pro to calculate stoplight based on duration:

IIf([Active] = 0, -1, IIf([Scheduled Duration] = 0, (IIf([% Complete] = 100, 2, IIf([Scheduled Finish] < Now() + 7 And [% Complete] < 100 And [% Complete] >= 80, 3, IIf([Scheduled Finish] < Now() + 7 And [% Complete] < 80, 4, 1)))), IIf(([% Complete] = 100 Or [% Complete] > 100 * (Abs((Now() - [Scheduled Start]) / ([Scheduled Finish] - [Scheduled Start])))), 2, (IIf([Scheduled Finish] > Now(), IIf([Scheduled Start] > Now(), 1, 3), 4)))))

Formula in SSRS to calculate stoplight based on % work completed that does not work:

I believe the part of the formula I've bolded is where the problem is but I'm not sure how to correct it.

Thank you for any suggestions!

Daren Johnson

### All Replies

• Tuesday, October 18, 2011 1:58 PM

You can wrap your date fields and the now() function in CDate() to make sure all objects are of type Date.

Hope that helps!

Cheers!

Ryan
• Proposed As Answer by Tuesday, October 18, 2011 1:58 PM
• Unproposed As Answer by Tuesday, October 18, 2011 5:49 PM
•
• Tuesday, October 18, 2011 2:17 PM

Thank you for the note Ryan,

I replaced the bold section above with:

...and now I'm getting an error that says: Operator '/' is not defined for types 'System.TimeSpan' and 'System.TimeSpan'

Full formula is now:

Daren Johnson
• Tuesday, October 18, 2011 2:33 PM

Yeah, I can repro that.  Looks like you may need to use a custom assembly that can take the .ToString() vaue of the timespan, convert it to a number, divide, and then turn that back into a timespan through the timespan.Parse() method.  For more on custom assemblies, check here: http://support.microsoft.com/kb/920769

Cheers!

Ryan
• Tuesday, October 18, 2011 2:48 PM

Ok, well... I officially just got lost.  :(

May have to look for another way to show project actual vs. planned % work complete.  If there are other ways to do this in SSRS, I'd be more than willing to give them a try.

Thanks!

Daren Johnson
• Tuesday, October 18, 2011 5:45 PM

I believe I've figured out how to pull the calculation differently.  Since the calculation is already made in a custom column in Project Pro, I can just pull in the value from that column into SRSS and assign a stoplight to the value.

Much easier.  :)

Daren Johnson
• Wednesday, May 09, 2012 4:20 PM

Hi Daren, I've been searching forums and I believe what you wrote here is closest to what I'm trying to achieve. If you have time and could elaborate a little more, I'm trying to take the "RAG / Stoplight" formula and get it to pull the duration from a custom column within project so I can edit this value within the column and get the calculation rather than editing the formula for example...

IIf([% Complete]<100 And (([Text1]="Hi" And DateDiff("d",date(),[Scheduled Finish])<=5) Or (DateDiff("d",[Scheduled Finish],date())>14)),"r",IIf([% Complete]<100 And ((DateDiff("d",[Scheduled Finish],date())<=14 And DateDiff("d",[Scheduled Finish],date())>5) Or ([Text1]="Med" And DateDiff("d",date(),[Scheduled Finish])<3)),"a","g"))

I want to be able to edit the formula above to pull the duration from a custom column. Any help or thoughts would be greatly appreciated.

• Wednesday, May 09, 2012 7:20 PM

Hey Dan,

I'm not quite sure if I understand what you are trying to do.  Are you referring to the "duration" as what is being calculated from the "DateDiff"?  If so, couldn't you change the [Scheduled Finish] to a [Custom Date]?

Daren Johnson

• Friday, May 11, 2012 2:57 PM

Hi Daren, Thank you very much for your response!

What I am trying to do is take these parts of the formula...

IIf([% Complete]<100 And (([Text1]="Hi" And DateDiff("d",date(),[Scheduled Finish])<=**5)**

(DateDiff("d",[Scheduled Finish],date())>**14))**

Namely the 5 and 14 for date duration in all the lines of the formula where this occurs, and get the formula to pull these values/numbers from a custom column that I have created in my project in Project 2010, I am able to do this in excel but cat find a clear way to do it within project. So that I can change this value in my custom column rather than editing the formula itself to show the change and pull the calculation that way. Please let me know if I'm in the right direction or how I would go about accomplishing this. Thank you again for all your input.

Original Formula

IIf([% Complete]<100 And (([Text1]="Hi" And DateDiff("d",date(),[Scheduled Finish])<=5) Or (DateDiff("d",[Scheduled Finish],date())>14)),"r",IIf([% Complete]<100 And ((DateDiff("d",[Scheduled Finish],date())<=14 And DateDiff("d",[Scheduled Finish],date())>5) Or ([Text1]="Med" And DateDiff("d",date(),[Scheduled Finish])<3)),"a","g"))

• Monday, May 14, 2012 3:59 PM

Dan,

If your variable could be set at the project level you could create a project custom number fields named "Days1" and "Days2".  You could then insert those fields into your formula:

IIf([% Complete]<100 And (([Text1]="Hi" And DateDiff("d",date(),[Scheduled Finish])<=**[Days1])**

(DateDiff("d",[Scheduled Finish],date())>**[Days2]))**

Would that get you what you're looking for?

Daren Johnson