3 Replies Latest reply on Dec 13, 2012 9:33 AM by annette.krey

    DATEDIFF returning Null values

    annette.krey

      I need a Gantt chart to show the timeline between RFP Issued and Bid/Proposal Due dates. My data table contains multiple date types (not just these 2) for each Opportunity ID

       

      I created a calculated field for my RFP Issued

      If[Milestones]='RFP Issued'then[Milestone Date]end

       

      I created another one for my Bid/Proposal Due

      If[Milestones]='Bid/Proposal Due' then [Milestone Date]end

       

      I created the following Proposal Timeline DATEDIFF calculation

      DATEDIFF('day',[RFP Issued],[Bid/Proposal Due])

       

      I put my Opp ID and the 2 date calculation fields onto my Rows to make sure I was getting correct dates and I am, however, I am also getting some NULL's which I think has something to do with there being other dates tied to the Opportunities.

       

      I moved my RFP Issued field on the Columns, removed the Bid/Proposal Due field and added the Proposal Timeline DATEDIFF to the Size.

       

      My timeline is starting at the correct place (RFP Issued) but the bars are all the same length since my DATEDIFF function is not calculating. I am wondering if this has to do with there being other dates in the Opportunities. I need to link this with another data source for additional Opportunity information but don't want to try that until I manage to get the Gantt to work correctly.

       

      Any help would be appreciated.

        • 1. Re: DATEDIFF returning Null values
          Tracy Rodgers

          Hi Annette,

           

          I have reattached your workbook with the what I think is the desired view. Instead of using two separate calculations for RFP and Bid/Proposal, I created 1 calculation that combines the two--then filtered out the NULL values.

           

          Then, the datediff calculation was created to use the previous date (refer to Sheet 6). Then, how it is computed needs to be adjusted (right click on it on the text shelf and select Edit Table Calculation to see how it's being computed).

           

          Hope this helps!

           

          -Tracy

          • 2. Re: DATEDIFF returning Null values
            annette.krey

            Yes, that is exactly what I was looking for. Is there anyway to get both the RFP Issued and Bid/Proposal Dates to display in the tool tip, it currently displays the RFP Issue date twice.

             

            Nice work, thanks!

             

            Annette

            1 of 1 people found this helpful
            • 3. Re: DATEDIFF returning Null values
              annette.krey

              I was working on it again this morning and have a couple more questions.

               

              The bars are displaying as I want but the DATEDIFF counts look to be repeating on the bar

               

              Example:

              Opp ID 00000002

              RFP Issued 05/02/2012

              Bid/Proposal Due 11/29/2012

               

              DATEDIFF identifies 211 days (excel calculates 207) however when you click on the bar you have 2 sections in it, one has the correct DATEDIFF range and then the other section adds on another 211 days so the bar is ending in June 2013.

               

              Some of the opps still have Nulls

               

              Opp ID 20000024

              RFP Issued 11/31/2012

              Bid/Proposal Due 01/31/2013

               

              DATEDIFF is returning a Null

               

              I also need to sort it so the list displays by RFP Issue date so we can tell what opportunities will be in house for proposal at the same time so we can resource plan. I can only find a sort by Opp ID number.

               

              Thank you in advance for your help.

               

              Annette