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

# Dates- Calculation

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

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

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

• ###### 3. Re: Dates- Calculation

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

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

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

• ###### 6. Re: Dates- Calculation

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

Thanks,

Will

• ###### 7. Re: Dates- Calculation

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

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

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.