Discussions

Expand all | Collapse all

The earning period runs for three months. How do I create a formula to result in date of the last day of month?

  • 1.  The earning period runs for three months. How do I create a formula to result in date of the last day of month?

    Posted 04-22-2019 14:10
    I have an 'Earning Period Start' date field and an 'Earning Period End' formula-date field. The earning period runs for three months. How do I create a formula for a three month earning period that results in the last day of the third month? (i.e. Earning Period Starts 1/1/2019, Earning Period ends 3/31/2019). This is the formula I started with but it gives me the date of the first day of the third month, not the last day: 

    =AdjustMonth([Earning Period Start],3)


  • 2.  RE: The earning period runs for three months. How do I create a formula to result in date of the last day of month?

    Top
    Contributor
    Posted 04-22-2019 14:18
    To get the last day of any month, I would subtract 1 day from the 1st of the next month. Hope that helps.


  • 3.  RE: The earning period runs for three months. How do I create a formula to result in date of the last day of month?

    Posted 04-22-2019 14:43
    or else

    LastDayOfMonth(AdjustMonth([Earning Period Start],3))


  • 4.  RE: The earning period runs for three months. How do I create a formula to result in date of the last day of month?

    Posted 04-22-2019 14:51
    Thanks! This worked: AdjustMonth([Earning Period Start],3)-Days(1)


  • 5.  RE: The earning period runs for three months. How do I create a formula to result in date of the last day of month?

    Top
    Contributor
    Posted 04-22-2019 15:21
    I think that will result in the last day of the 2nd month. I would try AdjustMonth([Earning Period Start],4)-Days(1)