You need to sign in to do that
Don't have an account?

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.
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.
All Answers
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.
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?
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
))
)
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.
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))
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 )) )
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!
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.
So you want to be a millionaire,eh??
I knew I came to the right place when I came here for answers.