Calculation of days from the beginning of the month

  • I used this formula to calculate how many days a client was living in a facility since the beginning of the month, discounting 2 days that this person was out and no release date available.
    =IF(ISBLANK(D2),TODAY()-C2,D2-C2)-E2
    I want to do is add to this formula the following condition: Use the admission date as the beginning of the month if the admission date is later than the beginning of the month. [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 804"]

    [tr]


    [td]

    1st. Day/Month (A)

    [/td]


    [td]

    Name (B)

    [/td]


    [td]

    Admission Date (C)

    [/td]


    [td]

    Release Date (D)

    [/td]


    [td]

    # Of days out (E)

    [/td]


    [td]

    # of Days Since Beginning of the Month (F)

    [/td]


    [td]

    # of Days In Since Admission. (G)

    [/td]


    [/tr]


    [tr]


    [td]

    08/01/18

    [/td]


    [td]

    AAA

    [/td]


    [td]

    08/10/18

    [/td]


    [td][/td]


    [td]

    3

    [/td]


    [td][/td]


    [td]

    4

    [/td]


    [/tr]


    [tr]


    [td]

    08/01/18

    [/td]


    [td]

    CCC

    [/td]


    [td]

    07/01/18

    [/td]


    [td][/td]


    [td]

    2

    [/td]


    [td]

    7

    [/td]


    [td]

    38

    [/td]


    [/tr]


    [/TABLE]

  • Hello,


    You could test the following :


    Code
    =IF(ISBLANK(D2),TODAY()-IF(C2>A2,C2,A2),D2-IF(C2>A2,C2,""))-E2


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello,


    Could you kindly attach your workbook ...with your next message ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello,


    Could you be more explicit ... than " it is not working " ....


    1. What should be the correct Number of Days in cell N2 ... ???


    2. What should be the correct Number of Days in cell N4 ... ???


    3. What is the calculation you are using to get these two correct Numbers ...???


    By the way ... please double check the formula located in cell N2 is identical to the ones in cells N3,N4,N5 ....



    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,

    [SIZE=13px]I am trying to calculate how many days since the beginning of the month this person was in the facility, discounting the days that for any reason was out.[/SIZE]


    Correct number on Line 2 is 6. (Beginning of the month: 09/01/2018, because admission date was in August. Release Date: 09/09/2018. Days out of the facility:2. Total of days this person was in my facility since the beginning of the month is: 6 days.


    Correct number on Line 4 is 3 (Beginning of the month: 09/02/2018, because the admission date is after the first day of the month; Release date 09/07/2018.Days out of the facility 2. Total of days this person was since the beginning of the month or admission date if admission is later of the beginning of the month: 3 days.


    Line 3 and 5 works if there is no release date on it, and the admission date is in the same month than column A. It does not work if the admission date is months before.


    Formulas


    Line 2: =IF(ISBLANK(J2),TODAY()-IF(G2>A2,G2,A2),IF(J2>A2,J2-A2,0))-M2


    Line 4: =IF(ISBLANK(J7),TODAY()-IF(G7>A7,G7,A7),IF(J7>G7,J7-G7,0))-M7

  • Hi Carim, The formulas are working. Thank you.


    Glad your problem is now fixed ..:wink:


    Thanks for your Thanks ...AND for the Like ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!