function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
alok29novalok29nov 

Help needed.. Date issue

Hi All,

 

I have four date fields in my poortunity object. Now, i have a requirement where I want to find out the latest date among four of them.  I have issue when I one of them  is blank. Formula does not return anyhting if any of them is blank. But my requirement is if there are let say two fields with dates then it should give me latest date of them ignoring two blank fields.

 If it can be solved through apex classes, i am fine with it.

 

Exaple:

 

Stage C | Champion Letter Date                   10-Nov-2011

Stage C | Solution [Key Players] Date

Stage C | Decision Maker Identified Date      8 Dec-2009

Stage C | Solution [Key Players] Date

 

New field should return 10-Nov-2011 i.e. latest of them all.

 

Thanks in advance!

 

~Alok

Best Answer chosen by Admin (Salesforce Developers) 
SrikanthKuruvaSrikanthKuruva

find below the formula if all the date fields are null. Remember now you have to replace each date field 8 times.

 

IF(IF(IF(NULLVALUE(d2__c,DATE(1970,1,1))>=NULLVALUE(d1__c,DATE(1970,1,1)),NULLVALUE(d2__c,DATE(1970,1,1)),NULLVALUE(d1__c,DATE(1970,1,1)))>=
IF(NULLVALUE(d4__c,DATE(1970,1,1))>=NULLVALUE(d3__c,DATE(1970,1,1)),NULLVALUE(d4__c,DATE(1970,1,1)),NULLVALUE(d3__c,DATE(1970,1,1))),IF(NULLVALUE(d2__c,DATE(1970,1,1))>=NULLVALUE(d1__c,DATE(1970,1,1)),NULLVALUE(d2__c,DATE(1970,1,1)),NULLVALUE(d1__c,DATE(1970,1,1)))
,IF(NULLVALUE(d4__c,DATE(1970,1,1))>=NULLVALUE(d3__c,DATE(1970,1,1)),NULLVALUE(d4__c,DATE(1970,1,1)),NULLVALUE(d3__c,DATE(1970,1,1))))<>DATE(1970,1,1),IF(IF(NULLVALUE(d2__c,DATE(1970,1,1))>=NULLVALUE(d1__c,DATE(1970,1,1)),NULLVALUE(d2__c,DATE(1970,1,1)),NULLVALUE(d1__c,DATE(1970,1,1)))>=
IF(NULLVALUE(d4__c,DATE(1970,1,1))>=NULLVALUE(d3__c,DATE(1970,1,1)),NULLVALUE(d4__c,DATE(1970,1,1)),NULLVALUE(d3__c,DATE(1970,1,1))),IF(NULLVALUE(d2__c,DATE(1970,1,1))>=NULLVALUE(d1__c,DATE(1970,1,1)),NULLVALUE(d2__c,DATE(1970,1,1)),NULLVALUE(d1__c,DATE(1970,1,1)))
,IF(NULLVALUE(d4__c,DATE(1970,1,1))>=NULLVALUE(d3__c,DATE(1970,1,1)),NULLVALUE(d4__c,DATE(1970,1,1)),NULLVALUE(d3__c,DATE(1970,1,1)))),null)

 

All Answers

SrikanthKuruvaSrikanthKuruva

following is the formula you can copy paste the same and replace d1__c, d2__c, d3__c, d4__c with the api names of your date fields. each of the date field occurs 4 times in the formula. so you will have to replace each date field 4 times.

I created the formula with the assumption that atleast one of the date fields is non-blank and the date fields are greater than or equal to January 1st, 1970. if this is not the case let me know we can change the formula.

 

IF(IF(NULLVALUE(d2__c,DATE(1970,1,1))>=NULLVALUE(d1__c,DATE(1970,1,1)),NULLVALUE(d2__c,DATE(1970,1,1)),NULLVALUE(d1__c,DATE(1970,1,1)))>=
IF(NULLVALUE(d4__c,DATE(1970,1,1))>=NULLVALUE(d3__c,DATE(1970,1,1)),NULLVALUE(d4__c,DATE(1970,1,1)),NULLVALUE(d3__c,DATE(1970,1,1))),IF(NULLVALUE(d2__c,DATE(1970,1,1))>=NULLVALUE(d1__c,DATE(1970,1,1)),NULLVALUE(d2__c,DATE(1970,1,1)),NULLVALUE(d1__c,DATE(1970,1,1)))
,IF(NULLVALUE(d4__c,DATE(1970,1,1))>=NULLVALUE(d3__c,DATE(1970,1,1)),NULLVALUE(d4__c,DATE(1970,1,1)),NULLVALUE(d3__c,DATE(1970,1,1))))

 

alok29novalok29nov

Hi Srikantha,

 

Thank you so much  for putting so much effort. Actually there are some opportunites where all the fields are blank.

 

I will check this for the opportunities which have value in atleast one date field.

 

Thanks again.

 

~Alok

alok29novalok29nov

Just wanted to add one more thing...All the four fields are datetime.

alok29novalok29nov

One more issue Srikantha..

 

You have used date method which will return date only but my reuirement is to get date with month and year too.

 

Like if two fields have dates 04-10-2010 and 07-06-2008. Now the formula fields should return 04-10-2010.

 

~Alok

alok29novalok29nov

Hey, it is working fine for date fileds. Just need to tweak for datevalue fileds. Can ypu please give me the formula if all fileds are null.

 

you are spot on. Thank you so much.

 

~Alok

SrikanthKuruvaSrikanthKuruva

find below the formula if all the date fields are null. Remember now you have to replace each date field 8 times.

 

IF(IF(IF(NULLVALUE(d2__c,DATE(1970,1,1))>=NULLVALUE(d1__c,DATE(1970,1,1)),NULLVALUE(d2__c,DATE(1970,1,1)),NULLVALUE(d1__c,DATE(1970,1,1)))>=
IF(NULLVALUE(d4__c,DATE(1970,1,1))>=NULLVALUE(d3__c,DATE(1970,1,1)),NULLVALUE(d4__c,DATE(1970,1,1)),NULLVALUE(d3__c,DATE(1970,1,1))),IF(NULLVALUE(d2__c,DATE(1970,1,1))>=NULLVALUE(d1__c,DATE(1970,1,1)),NULLVALUE(d2__c,DATE(1970,1,1)),NULLVALUE(d1__c,DATE(1970,1,1)))
,IF(NULLVALUE(d4__c,DATE(1970,1,1))>=NULLVALUE(d3__c,DATE(1970,1,1)),NULLVALUE(d4__c,DATE(1970,1,1)),NULLVALUE(d3__c,DATE(1970,1,1))))<>DATE(1970,1,1),IF(IF(NULLVALUE(d2__c,DATE(1970,1,1))>=NULLVALUE(d1__c,DATE(1970,1,1)),NULLVALUE(d2__c,DATE(1970,1,1)),NULLVALUE(d1__c,DATE(1970,1,1)))>=
IF(NULLVALUE(d4__c,DATE(1970,1,1))>=NULLVALUE(d3__c,DATE(1970,1,1)),NULLVALUE(d4__c,DATE(1970,1,1)),NULLVALUE(d3__c,DATE(1970,1,1))),IF(NULLVALUE(d2__c,DATE(1970,1,1))>=NULLVALUE(d1__c,DATE(1970,1,1)),NULLVALUE(d2__c,DATE(1970,1,1)),NULLVALUE(d1__c,DATE(1970,1,1)))
,IF(NULLVALUE(d4__c,DATE(1970,1,1))>=NULLVALUE(d3__c,DATE(1970,1,1)),NULLVALUE(d4__c,DATE(1970,1,1)),NULLVALUE(d3__c,DATE(1970,1,1)))),null)

 

This was selected as the best answer
alok29novalok29nov

It worked...thanks!!

 

I want the formula for three fields and two fileds too. I would try to write the formula mysely but it is a bit scary as it is too big.

Hope it  does not consume my whole day. I appreciate your formula writing skills man. You can write big fromulas as complex as it is. hats off to u.

 

Thanks

Alok

alok29novalok29nov

Hi Srikanth,

 

Can you please give me the formula for three field as well? I tried but could not write it correctly.

 

~Alok

SrikanthKuruvaSrikanthKuruva

i have seen a simpler solution in your other post

viz.

http://boards.developerforce.com/t5/Formulas-Validation-Rules/Urgent-help-required/m-p/357609

 

let me know if it does not work we shall work it out.

 

alok29novalok29nov

I did not check that as your solution was working fine. I'll check and let you know. That really looks a simpler solution.

alok29novalok29nov

Hi Srikantha,

 

That formula is also working fine. That is a lot simpler and I can create the formula for three fileds myself.

 

Thanks for suggesting that!