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
Bill DemuthBill Demuth 

complex date formula

I am working with compex commision formulas.

I have 95 percent worked out, but have a final challenge

I think I need a custom field,  a checkbox, which answers a simple question

Is the "Billing Date" field value OLDER than the first of last month.

Will start as no, and will change to yes, Would be easy if all months had equal days but they do not :(

I am open to other suggestions but any of them will need some formula to anser this question

Thanks in advance!!
PRAKASH JADA 13PRAKASH JADA 13
Hi,


You need a checkbox with formula like the below.

Billing_Date__c  >  DATE(YEAR(Billing_Date__c),  MONTH(Billing_Date__c)-1, 1)
 
Alain CabonAlain Cabon
@Bill Demuth

The last day of a month is also the previous day of the first of the next month (and here it is always 1st ). 

We calculate it easily by building a date like: date ( year ( mydate__c ) , month ( mydate__c ) + 1 , 1 ) - 1   (  month + 1, 1st , - 1 day )

There are just a case for December and it is always 31st of December for the last day.

IF( MONTH( mydate__c ) = 12, DATE( YEAR( mydate__c ), 12, 31 ), DATE( YEAR( mydate__c ), MONTH( mydate__c  ) + 1, 1) - 1)

 
Bill DemuthBill Demuth
I used a similar approach, but selected the first day of the previous month, which more suited my circumstance! Thanks William C. DeMuth PKA Technologies, Inc Direct: 845-738-2318 Main: 845.357.0170 www.pkatech.com