You need to sign in to do that
Don't have an account?
Lynne Wiegley
Compile Error on DATE conversion if field blank
The following formula works to convert an integer (First_Order_Fcst_Period__c) to a date. Since we are dealing with fiscal years, with October being the first month of the fiscal year, you will see the calcuation below.
DATE(
(If( First_Order_Fcst_Period__c > 9,
Fiscal_Year_As_Integer__c -1,
Fiscal_Year_As_Integer__c )) ,
First_Order_Fcst_Period__c ,15)
However, if However, if First_Order_Fcst_Period__c is blank, I am getting is blank, I am getting an error. I changed my formula to be
IF(ISBLANK(First_Order_Fcst_Period__c),NULL,
DATE(
(If( First_Order_Fcst_Period__c > 9,
Fiscal_Year_As_Integer__c -1,
Fiscal_Year_As_Integer__c )) ,
First_Order_Fcst_Period__c ,15))
and now I get a compile error. I suspect it's because First_Order_Fcst_Period__c is rather large
(
IF(October_Order_Fcst__c > 0 || October_Order_Fcst__c <0,10,
IF(November_Order_Fcst__c >0 || November_Order_Fcst__c <0,11,
IF(December_Order_Fcst__c >0 || December_Order_Fcst__c <0,12,
IF(January_Order_Fcst__c >0 || January_Order_Fcst__c <0,1,
IF(February_Order_Fcst__c >0 || February_Order_Fcst__c <0,2,
IF(March_Order_Fcst__c >0 || March_Order_Fcst__c <0,3,
IF(April_Order_Fcst__c >0 || April_Order_Fcst__c <0,4,
IF(May_Order_Fcst__c >0 || May_Order_Fcst__c <0,5,
IF(June_Order_Fcst__c >0 || June_Order_Fcst__c <0,6,
IF(July_Order_Fcst__c >0 || July_Order_Fcst__c <0,7,
IF(August_Order_Fcst__c >0 || August_Order_Fcst__c <0,8,
IF(September_Order_Fcst__c>0|| September_Order_Fcst__c <0,9,Null)))))))))))))
Where is the best way to update this so as not to get a compile error
DATE(
(If( First_Order_Fcst_Period__c > 9,
Fiscal_Year_As_Integer__c -1,
Fiscal_Year_As_Integer__c )) ,
First_Order_Fcst_Period__c ,15)
However, if However, if First_Order_Fcst_Period__c is blank, I am getting is blank, I am getting an error. I changed my formula to be
IF(ISBLANK(First_Order_Fcst_Period__c),NULL,
DATE(
(If( First_Order_Fcst_Period__c > 9,
Fiscal_Year_As_Integer__c -1,
Fiscal_Year_As_Integer__c )) ,
First_Order_Fcst_Period__c ,15))
and now I get a compile error. I suspect it's because First_Order_Fcst_Period__c is rather large
(
IF(October_Order_Fcst__c > 0 || October_Order_Fcst__c <0,10,
IF(November_Order_Fcst__c >0 || November_Order_Fcst__c <0,11,
IF(December_Order_Fcst__c >0 || December_Order_Fcst__c <0,12,
IF(January_Order_Fcst__c >0 || January_Order_Fcst__c <0,1,
IF(February_Order_Fcst__c >0 || February_Order_Fcst__c <0,2,
IF(March_Order_Fcst__c >0 || March_Order_Fcst__c <0,3,
IF(April_Order_Fcst__c >0 || April_Order_Fcst__c <0,4,
IF(May_Order_Fcst__c >0 || May_Order_Fcst__c <0,5,
IF(June_Order_Fcst__c >0 || June_Order_Fcst__c <0,6,
IF(July_Order_Fcst__c >0 || July_Order_Fcst__c <0,7,
IF(August_Order_Fcst__c >0 || August_Order_Fcst__c <0,8,
IF(September_Order_Fcst__c>0|| September_Order_Fcst__c <0,9,Null)))))))))))))
Where is the best way to update this so as not to get a compile error
First_Order_Fcst_Period__c to:
(
IF(October_Order_Fcst__c <> 0,10,
IF(November_Order_Fcst__c <>0,11,
IF(December_Order_Fcst__c <>0 ,12,
IF(January_Order_Fcst__c <>0 ,1,
IF(February_Order_Fcst__c <>0 ,2,
IF(March_Order_Fcst__c <>0 ,3,
IF(April_Order_Fcst__c <>0 ,4,
IF(May_Order_Fcst__c <>0 ,5,
IF(June_Order_Fcst__c <>0 ,6,
IF(July_Order_Fcst__c <>0,7,
IF(August_Order_Fcst__c <>0 ,8,
IF(September_Order_Fcst__c<>0,9,Null)))))))))))))
Thx