Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
dishotton

# 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.

Best Answer chosen by Admin (Salesforce Developers)
Buell

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
))
)

And this does account for leap years.

Message Edited by Buell on 06-05-2009 04:38 PM

Jakester

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.

dishotton

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?

Buell

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
dishotton
Brilliant!  Thanks so much Buell!
Jakester

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.

Buell
True, leap years suck.
Jakester

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))

Buell

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 )) )

dishotton

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!

Buell
The last one I posted will take care of leap years, it just compiles to around 4K characters.  Jakester?
Jakester
Yeah, I'd say use Buell's last posted complete formula - should work just fine in 99% of cases.
Buell
Hmm... anything in particular you're noticing?
Jakester
Just the leap-year thing.
Buell
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.
Jakester
LOL, sorry, I just meant the code you provided that didn't provision for leap year :-)
Buell

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
))
)

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
dishotton

So you want to be a millionaire,eh??

I knew I came to the right place when I came here for answers.

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