0

I have a formulas of days in this month and this year with start and finish dates. My formula counts the number of days in this month/year that fall in a given interval (between Start Date and Close Date). For example, I have Start Date 30.12.2018, and Finish Date 03.01.2019. Days of this (2019) year should be 2 (without last day), and days of this (May) month should be 0.

So, I'm trying to count the number of days between the Start Date and CloseDate that fall in the current Month, and the number of days between the Start Date and CloseDate that fall in the current Year.

But I have troubles with big intervals through the years.

enter image description here

enter image description here

enter image description here

How can I fix these formulas?

Counting days for this month:

IF(
    AND(
        MONTH(TODAY()) = MONTH(Start_Date__c),
        MONTH(TODAY()) = MONTH(CloseDate)
    ),
    Days__c,
    IF(
        AND(
            MONTH(TODAY()) > MONTH(Start_Date__c),
            MONTH(TODAY()) = MONTH(CloseDate)
        ),
        Days__c - (
            (
                TODAY() - DAY(TODAY()) + 1
            ) - Start_Date__c
        ),
        IF(
            AND(
                MONTH(TODAY()) = MONTH(Start_Date__c),
                MONTH(TODAY()) < MONTH(CloseDate)
            ),
            IF(
                MONTH(TODAY()) = 12,
                DATE(YEAR(TODAY()), 12, 31),
                DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 2) - 1
            ) - Start_Date__c,
            IF(
                AND(
                    MONTH(TODAY()) > MONTH(Start_Date__c),
                    MONTH(TODAY()) < MONTH(CloseDate)
                ),
                DAY(
                    ADDMONTHS(
                        DATE(
                            YEAR(TODAY()), MONTH(TODAY()), 01
                    ), 1
                ) - 1
            ),
            0
        )
    )
)
)

Counting days for this year:

IF(
    AND(
        YEAR(TODAY()) = YEAR(Start_Date__c),
        YEAR(TODAY()) = YEAR(CloseDate)
    ),
    Days__c,
    IF(
        AND(
            YEAR(TODAY()) > YEAR(Start_Date__c),
            YEAR(TODAY()) = YEAR(CloseDate)
        ),
        Days__c - (
            (
                TODAY() - DAY(TODAY()) + 1
            ) - Start_Date__c
        ),
        IF(
            AND(
                YEAR(TODAY()) = YEAR(Start_Date__c),
                YEAR(TODAY()) < YEAR(CloseDate)
            ),
            (DATE(YEAR(TODAY()), 12, 31) + 1) - TODAY(),
            IF(
                AND(
                    YEAR(TODAY()) > YEAR(Start_Date__c),
                    YEAR(TODAY()) < YEAR(CloseDate)
                ),
                TODAY() - DATE(YEAR(TODAY()), 1, 1) + 1,
            0
        )
    )
)
)

Thanks for your help.

  • Could you please edit your post to use words to clearly describe what you want this formula to do? – Adrian Larson May 6 '19 at 14:58
  • Thank you. Done – MichaelLev19 May 6 '19 at 15:05
  • Your requirements are still not very clear. You have two date fields, and is not obvious how your formulas are supposed to interact with them. – Adrian Larson May 6 '19 at 15:29
  • I'm trying to count the number of days between the Start Date and CloseDate that fall in the current Month, and the number of days between the Start Date and CloseDate that fall in the current Year. – MichaelLev19 May 6 '19 at 15:49
  • This seems like logic which would be much more easily implemented via Apex Trigger. – Adrian Larson May 6 '19 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.