You need to sign in to do that
Don't have an account?
Haralamby
EOMONTH Function in Excel - Please help!!!
Hello,
I am trying to find a formula/function in Salesforce that is the equivalent to the EOMONTH function in excel. The excel function is as follows:
Scenario 1 =EOMONTH(A1,0) => this gives me the end date of the month that is in cell "A1"
Scenario 2 =EOMONTH(A1,1) = > this give me the end date of the month after the month that is in cell "A1"
So if A1 is Jan 15,2009 the function would give me Jan 31, 2009 in scenario 1 and Feb 28, 2009 in scenario 2.
Does anyone know how this can be accomplished in Salesforce?
Hi,
Actually the given formula can use only in Visual Force Page and Controller.
For Formulat field, use below formula,
Scenario 1 = DATE(YEAR(CustomDate),MONTH(CustomDate)+1,1)-1
Scenario 2 = DATE(YEAR(CustomDate),MONTH(CustomDate)+2,1)-1
This will sure solve your problem.
For VF page and controller below code will useful to you.
-------------------------------------------------------------
Page:
-------------------------------------------------------------
-------------------------------------------------------------
Controller:
-------------------
Thanks,
Kamlesh.
All Answers
Afaik there is no such function in Apex. However you could wrap a combination of the follow date functions in a custom method that would accomplish the same goal :
Date.addMonths(1)
Date.toStartOfMonth()
Date.addDays(-1)
David
Hello,
For Scenario 1 : Date.addDays(-Date.day()).addMonths(1)
For Scenario 2 : Date.addDays(-Date.day()).addMonths(2)
Thanks,
Kamlesh
When I use:
Date.addDays(-Date.day()).addMonths(2)
I get:
Error: Syntax error. Extra '.' This is for the period in ".addmonths"
Also; suppose the field that my date is in is called "Custom Date" where in the formula can insert that field?
Thanks!!!
Hi,
Actually the given formula can use only in Visual Force Page and Controller.
For Formulat field, use below formula,
Scenario 1 = DATE(YEAR(CustomDate),MONTH(CustomDate)+1,1)-1
Scenario 2 = DATE(YEAR(CustomDate),MONTH(CustomDate)+2,1)-1
This will sure solve your problem.
For VF page and controller below code will useful to you.
-------------------------------------------------------------
Page:
-------------------------------------------------------------
-------------------------------------------------------------
Controller:
-------------------
Thanks,
Kamlesh.
I don't know how to use VisualForce but all I wanted was a formula that is as robust as Excel but I guess that doesn't exist.
The formula below will not work consistently
Scenario 2 = DATE(YEAR(CustomDate),MONTH(CustomDate)+2,1)-1
Say for instance my custom date was July 20, 2009 and I want the end of the month for 7 months after July 20, 2009. The formulla above will give me an error because it doesn't add 7 months to my date but rather just inputs the year that the Custom Date currently has.
If you have any more suggestions let me know and thanks for all your help!!!!!
Hi,
Previous formula was made for only current and next month. Here is your new dynamic formula for any month.
DATE(YEAR(CustomDate) + FLOOR((MONTH(CustomDate)+CustomMonth+1)/12),MOD(MONTH(CustomDate)+CustomMonth+1,12),1)-1
Just replace your date with "CustomDate" and month with "CustomMonth".
Thanks,
Kamlesh