Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
dishottondishotton 

Date Formula

I'm looking for help on a date formula for twofields - Start Date and End Date. 

 

Start Date = If created date between 1 and 15 of createddate month, return MM01YYYY;  if createddate between 16 and last day of created date month, return MM16YYYY.

 

End Date = If Start Date is MM01YYYY, returnMM15YYYY;  if Start Date is MM16YYYY,return last day of month.

 

These are all calendar dates.  

 

Thanks in advance. 

Best Answer chosen by Admin (Salesforce Developers) 
BuellBuell

Alright dishotton:

 

 

Start Date (Date):

 

DATE( YEAR( DATEVALUE(CreatedDate) ), 
MONTH( DATEVALUE(CreatedDate) ), 
IF( DAY ( DATEVALUE(CreatedDate)) <16, 1, 16)
)
 
End Date (Date):
 
DATE( YEAR( DATEVALUE(CreatedDate) ), 
MONTH( DATEVALUE(CreatedDate) ), 
IF( DAY ( DATEVALUE(CreatedDate)) <16, 16, 
CASE( MONTH( DATEVALUE(CreatedDate) ), 
4,30, 
6,30, 
9,30, 
11,30, 
2, IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 400) = 0, 29, 
IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 100) = 0, 28, 
IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 4) = 0, 29, 
28))), 
31 
)) 
)
 
Final answer...
 
And this does account for leap years.  
 
Message Edited by Buell on  06-05-2009 04:38 PM


All Answers
JakesterJakester
That should be a really easy one. Untested, but something like this should do the trick:
 
Start Date  (Text Formula)
 
if(day(createddate)<16  ,month(createddate)&01&year(createddate)  ,month(createddate)&16&year(createddate))
 
End Date (Text Formula based on the Start Date formula)
 
if(mid(start_date__c,3,2)="01"  ,left(start_date__c,2)&"01"&right(start_date__c,4)  ,left(start_date__c,2)&IF(Month(createddate)=12, 31, DAY(DATE(YEAR(createddate),MONTH(createddate)+1,1) - 1))&right(start_date__c,4)
 
 
 Whew. Ok, so most of it was really easy. The hard part was the "last day of month" bit, but hopefully that'll work. I found it, believe it or not, in Salesforce's help in their validation examples.
 
 
 
 
 


dishottondishotton
Hey Jakester...thanks for repsonding.  So I tested these today and here's what I found.
 
Start Date error on created date says Error: Incorrect parameter for function day(). Expected Date, received DateTime
so i guess the new date is looking to match the same data type field.
 
on the End Date  Error: Incorrect parameter for function mid(). Expected Text, received Date
 
Ideas?
 
 
 


BuellBuell
Try this...
 
Start Date (Date)
 
 
DATE( YEAR( DATEVALUE(CreatedDate) ), 
MONTH( DATEVALUE(CreatedDate) ), 
IF( DAY ( DATEVALUE(CreatedDate)) <16, 1, 16)
)
 
End Date (Date)
 
DATE( YEAR( DATEVALUE(CreatedDate) ), 
MONTH( DATEVALUE(CreatedDate) ), 
IF( DAY ( DATEVALUE(CreatedDate)) <16, 16, 
CASE( MONTH( DATEVALUE(CreatedDate) ), 
4,30, 
6,30, 
9,30, 
11,30, 
2,28, 
31 
)) 
)
 
Message Edited by Buell on  06-02-2009 11:00 AM


dishottondishotton
Brilliant!  Thanks so much Buell!


JakesterJakester
Awesome, Buell!
 
One minor nit that won't matter for a long time, but technically always making Feb on the 28th will be wrong on leap-years.


BuellBuell
True, leap years suck.


JakesterJakester
LOL, yeah-
 
I'm pretty sure this little snippet handles everything, including leap years, though:
IF(Month(createddate)=12, 31, DAY(DATE(YEAR(createddate),MONTH(createddate)+1,1) - 1))
 
 
 
 


BuellBuell
Nicely done.  That is more elegant than my solution:
 
 
 
DATE( YEAR( DATEVALUE(CreatedDate) ), 
MONTH( DATEVALUE(CreatedDate) ), 
IF( DAY ( DATEVALUE(CreatedDate)) <16, 16, 
CASE( MONTH( DATEVALUE(CreatedDate) ), 
4,30, 
6,30, 
9,30, 
11,30, 
2, IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 400) = 0, 29, 
IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 100) = 0, 28, 
IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 4) = 0, 29, 
28))), 
31 
)) 
)
 
 
 


dishottondishotton
Thanks both of you.  last question:  where does Jakester's formula fit with Buell's formula?
i am formula challenged and would love to have the "whole" thing if possible.
 
Thanks again!


BuellBuell
The last one I posted will take care of leap years, it just compiles to around 4K characters.  Jakester?


JakesterJakester
Yeah, I'd say use Buell's last posted complete formula - should work just fine in 99% of cases.


BuellBuell
Hmm... anything in particular you're noticing?


JakesterJakester
Just the leap-year thing.


BuellBuell
What? You doubt wikipedia's leap year formula?! :)  I'm pretty sure the last revised formula will sort out all leap years unless you go back before 1500. 


JakesterJakester
LOL, sorry, I just meant the code you provided that didn't provision for leap year :-)


BuellBuell
Alright dishotton:
 
 
Start Date (Date):
 
DATE( YEAR( DATEVALUE(CreatedDate) ), 
MONTH( DATEVALUE(CreatedDate) ), 
IF( DAY ( DATEVALUE(CreatedDate)) <16, 1, 16)
)
 
End Date (Date):
 
DATE( YEAR( DATEVALUE(CreatedDate) ), 
MONTH( DATEVALUE(CreatedDate) ), 
IF( DAY ( DATEVALUE(CreatedDate)) <16, 16, 
CASE( MONTH( DATEVALUE(CreatedDate) ), 
4,30, 
6,30, 
9,30, 
11,30, 
2, IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 400) = 0, 29, 
IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 100) = 0, 28, 
IF( MOD( YEAR( DATEVALUE(CreatedDate) ), 4) = 0, 29, 
28))), 
31 
)) 
)
 
Final answer...
 
And this does account for leap years.  
 
Message Edited by Buell on  06-05-2009 04:38 PM
This was selected as the best answer
dishottondishotton
So you want to be a millionaire,eh??
I knew I came to the right place when I came here for answers.
 


JakesterJakester
Be sure to click the Accepted Solution so Buell gets his propers.