9 Replies Latest reply on Feb 18, 2016 7:50 AM by ChandraSheker A

    Dates- Calculation

    ChandraSheker A

      Hi all,

      Please Find attachment for the Sample excel file,

      My Question is: If, an ID Has Date3, i want to see same date in Date2 and Date1 columns,

                                If, an ID has Date2, I want to see Same date in Date1,

                         and If, an ID has Date2 and Date3, I want to see Date2 in Date1.

                         and If, and ID has Date1 and Date3, I want to see Date3 in Date2.

       

       

      And Finally I want to Count the Number of IDs, Count of Date1,date2,date3.

      Like                                                   500                      300           150      50

       

      Please Help me out in calculating the above because my logic is if an ID has date3 it should have date2 and date1 but not Null.

                                                                                                                     ID has Date2 it should have date1 but not Null.

       

       

      Let me know if i am not clear.

      Thanks.

        • 1. Re: Dates- Calculation
          Will Conklin

          These may work for you:

           

          New Date1:

           

          IF ISNULL([Date2 1]) THEN

              IF NOT ISNULL([Date3]) THEN [Date3]

              ELSEIF NOT ISNULL([Date2]) THEN [Date2]

              END

          ELSE [Date2 1]

          END

           

           

          New Date2:

           

          IF ISNULL([Date2]) THEN

              IF NOT ISNULL([Date3]) THEN [Date3]

              END

          ELSE [Date2]

          END

           

          Leave Date3 the same.

           

          Cheers,

           

          Will

          1 of 1 people found this helpful
          • 2. Re: Dates- Calculation
            Will Conklin

            Then you can take a COUNTD of all of the dimensions.

            • 3. Re: Dates- Calculation
              ChandraSheker A

              Thank you Will,

               

              Above calculation for New Date1 is Close enough for me, But if i have a date1 and Date2 for an ID,

              Above calculation replaced my actual Date1 to date2.

              If there exist date1 it shouldn't be replaced by date2, 

              Similarly if i had Date 2, it shouldn't be replaced by date3

               

              Hope i am clear to you

              • 4. Re: Dates- Calculation
                Will Conklin

                Hmm I have looked through my sample workbook, and I can't find an instance of where this is happening

                 

                 

                 

                 

                IF ISNULL([Date2 1]) THEN

                    IF NOT ISNULL([Date3]) THEN [Date3]

                    ELSEIF NOT ISNULL([Date2]) THEN [Date2]

                    END

                ELSE [Date2 1]

                END

                 

                The logic of the original calculation says, "If Original Date 1 is Null, then replace it with either Date 2 or Date 3, If Date 1 is not null, keep it and don't do anything"

                 

                So if there is a value in Date 1, it shouldn't be overwritten the same logic applies for New Date 2. I would make sure your calculation is the same as what I originally posted. There may be something else in your workbook (a filter, etc) that could be causing the calculation to return different results than what I have in my sample workbook.

                 

                 

                Thanks,

                 

                Will

                • 5. Re: Dates- Calculation
                  ChandraSheker A

                  I see a Pill in above Pic, Date2 1 is that Date1?

                  • 6. Re: Dates- Calculation
                    Will Conklin

                    In your data set provided I assumed "Date2 1" was Date 1

                     

                    Thanks,

                     

                    Will

                    • 7. Re: Dates- Calculation
                      ChandraSheker A

                      Well that made me Confused,I was using Date2 1 as date2,

                       

                      Now its all Good.

                       

                      THANKS a LOT Will it really helped me.

                       

                      But there are few ID i see my my own data set before and  after applying your calculations.

                          ID             Date1                       Date2                          Date3

                           ID                   NEW Date1            New Date2               Date3

                       

                      in above pic, you can see the New Date1 is replaced by Date3, But i wanted to see New date1 with Date2 as this exist.

                      Can you change the calculation for the NEW date1, If there is Date2 and Date3, the New Date should be replaced with Date2.

                       

                      New date1<= Newdate2<=Date3 (This is what i am looking as my business logic)

                      • 8. Re: Dates- Calculation
                        Will Conklin

                        Ah I think I had the order backwards in the calc.

                         

                        Try this:

                         

                        IF ISNULL([Date2 1]) THEN

                            IF NOT ISNULL([Date2]) THEN [Date2]

                            ELSEIF NOT ISNULL([Date3]) THEN [Date3]

                            END

                        ELSE [Date2 1]

                        END

                         

                         

                        Cheers!

                         

                        Will

                        1 of 1 people found this helpful
                        • 9. Re: Dates- Calculation
                          ChandraSheker A

                          Thats Works like a gem.

                           

                          Thank a Lot Will.

                          will let you know if i found any other issues in my dataset.

                          as of now all looks perfect.