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
Anne Larsen 2Anne Larsen 2 

The following formula field shows Error#! in my report when the Order Date is 2/29/2016 (leap year). Anyone know how to fix?

 IF(  AND( YEAR(TODAY())-1 = YEAR(OrderDate__c), TODAY()-1 >= Date(year(Today()), month(OrderDate__c), day(OrderDate__c))),  OrderAmount__c, 0)

Thanks! Anne
Best Answer chosen by Anne Larsen 2
FearNoneFearNone
Hi Anne,

One way is to set ignore leap year dates...
IF( AND( YEAR(TODAY())-1 = YEAR(OrderDate__c), TODAY()-1 >= 
	IF(MONTH(OrderDate__c)=2 && DAY(OrderDate__c)=29,
        DATE(YEAR(Today()), 2, 28),
        DATE(YEAR(Today()), MONTH(OrderDate__c), DAY(OrderDate__c)))),  OrderAmount__c, 0)


Hope this can help somehow

All Answers

FearNoneFearNone
Hi Anne,

One way is to set ignore leap year dates...
IF( AND( YEAR(TODAY())-1 = YEAR(OrderDate__c), TODAY()-1 >= 
	IF(MONTH(OrderDate__c)=2 && DAY(OrderDate__c)=29,
        DATE(YEAR(Today()), 2, 28),
        DATE(YEAR(Today()), MONTH(OrderDate__c), DAY(OrderDate__c)))),  OrderAmount__c, 0)


Hope this can help somehow
This was selected as the best answer
Anne Larsen 2Anne Larsen 2
Thank you FearNone, worked like a charm, I really appreciate your help!
Anne Larsen 2Anne Larsen 2
So I have another question, my formula field is meant to show $ LYTD (last year to date).  So, if this ignores leap year dates, then when I get to March 2017, it will show 0 for the 2/29/2016 orders (instead of the true order amount)? I need a way for the opportunities with Order Date 2/29/2016 to show in that $ LYTD field. Is there a way to do that?
FearNoneFearNone
how about changing it to DATE(YEAR(Today()), 3, 1)?


but if you prefer the condition to be precise, let's add some leap-year computation...
IF(  AND( YEAR(TODAY())-1 = YEAR(OrderDate__c), TODAY()-1 >= 
	IF(AND(MONTH(OrderDate__c)=2 && DAY(OrderDate__c)=29,NOT( 
		OR( 
		MOD( YEAR(Today(), 400 ) = 0, 
		AND( 
			MOD(YEAR(Today(), 4) = 0,
			MOD(YEAR(Today(), 100) != 0
		)
    ))),
        DATE(YEAR(Today()), 3, 1),
        DATE(YEAR(Today()), MONTH(OrderDate__c), DAY(OrderDate__c)))),  OrderAmount__c, 0)

(there might be some syntax error on this)