+ Start a Discussion
HaralambyHaralamby 

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?

Best Answer chosen by Admin (Salesforce Developers) 
kamlesh_chauhankamlesh_chauhan

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:

-------------------------------------------------------------

<apex:page id="MytestPage" controller="TestController">

Scenario 1 :
<apex:outputText value="{!if (NOT(ISNULL(Date1)), '{0,date,long}', '')}">
<apex:param value="{!Date1}" />
</apex:outputText>
<BR/>
Scenario 2 :
<apex:outputText value="{!if (NOT(ISNULL(Date2)), '{0,date,long}', '')}">
<apex:param value="{!Date2}" />
</apex:outputText>
</apex:page>

 

------------------------------------------------------------- 

Controller:

-------------------

Public Class TestController {
    private Date TestDate = date.newInstance(2009,01,15);
    public Date getDate1() {
        Date d1 = TestDate.addDays(-TestDate.day()).addMonths(1);
        return d1;
    }
    public Date getDate2() {
        Date d2 = TestDate.addDays(-TestDate.day()).addMonths(2);
        return d2;
    }
}

Thanks,

Kamlesh.

 

All Answers

David VPDavid VP

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

kamlesh_chauhankamlesh_chauhan

Hello,

 

For Scenario 1 : Date.addDays(-Date.day()).addMonths(1)

For Scenario 2 : Date.addDays(-Date.day()).addMonths(2)

 

Thanks,

Kamlesh

HaralambyHaralamby

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

kamlesh_chauhankamlesh_chauhan

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:

-------------------------------------------------------------

<apex:page id="MytestPage" controller="TestController">

Scenario 1 :
<apex:outputText value="{!if (NOT(ISNULL(Date1)), '{0,date,long}', '')}">
<apex:param value="{!Date1}" />
</apex:outputText>
<BR/>
Scenario 2 :
<apex:outputText value="{!if (NOT(ISNULL(Date2)), '{0,date,long}', '')}">
<apex:param value="{!Date2}" />
</apex:outputText>
</apex:page>

 

------------------------------------------------------------- 

Controller:

-------------------

Public Class TestController {
    private Date TestDate = date.newInstance(2009,01,15);
    public Date getDate1() {
        Date d1 = TestDate.addDays(-TestDate.day()).addMonths(1);
        return d1;
    }
    public Date getDate2() {
        Date d2 = TestDate.addDays(-TestDate.day()).addMonths(2);
        return d2;
    }
}

Thanks,

Kamlesh.

 

This was selected as the best answer
HaralambyHaralamby

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

kamlesh_chauhankamlesh_chauhan

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