Forum Discussion

Edward Brown's avatar
Edward Brown
Copper Contributor
Sep 16, 2018

Create a cell IF formula based on Month

I need to create a cell in Excel that will work a formula during a certain month and will display a numeric value when it is not that particular month (it is for a daily expenditure value that automa...
Escel
Formulas and Functions
Edward Brown's avatar
Edward Brown
Copper Contributor
Sep 17, 2018

I tried your formula and it seemed to work with the exception of the math formula.  For some reason 30-DAY(TODAY())*100 returns a value of -1670.  I can't figure that one out.  I changed the "8" to "9" in your formula to correlate with September and I received that value.  I put only the above formula in with the same result.

 

Any thoughts?

 

Thank you for your help.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 17, 2018

Edward, and what do you expect to be returned?

 

Today is Sep 17th. DAY() of it =17. Multiply on 100 = 1700. 30 minus that gives -1670.

  • Edward Brown's avatar
    Edward Brown
    Copper Contributor
    Sep 17, 2018

    I want a formula that will count down by the day of the month.  This is for a cell in a budget sheet that displays the allotment for daily expenditures.

     

    As an example:

     

    The value for September 17th would be $1,300.00 (30-17*100)

    On September 18th the value would change to $1,200.00 (30-18*100).

     

    Thank you.  I really appreciate your help.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 17, 2018

      Edward, if you deduct $100 for each coming day then it'll be =sum-day(today())*100 or 3000-18*100 for Sep 18.

       

      I'm not sure about your business logic, if you have $3000 per month and countdown equally for each day that could be like

      =3000-3000/(EOMONTH(TODAY(),0)-EOMONTH(TODAY(),-1))*DAY(TODAY())

      since months have different numbers of days.

      If above magic $3000 is for Aug only when

      =MIN(3000*(1-1/(EOMONTH(TODAY(),0)-EOMONTH(TODAY(),-1))*DAY(TODAY()))*(MONTH(TODAY())=8),3000)+3000*(MONTH(TODAY())>8)

      But again, it depends on your exact business logic.

      • Edward Brown's avatar
        Edward Brown
        Copper Contributor
        Sep 17, 2018

        I looked at it more closely and realized I was missing a pair of parenthesis.  This formula now works for my application:

         

        August:

        =IF(MONTH(TODAY())=8,(31-DAY(TODAY()))*100, IF(MONTH(TODAY()) < 8,0,3100))

         

         

        In this formula, January - July (months that were already past) would register as $0.00 

        August would show a value that decrements with the day of the month

        September - December would register as $3,000.00 (I would change the value to accurately reflect the number of days in the month.

         

        This is what September formula would look like:

         

        September:

        =IF(MONTH(TODAY())=9,(30-DAY(TODAY()))*100, IF(MONTH(TODAY()) < 9,0,3000))

         

        I have a budget worksheet that has 12 tabs, one for each month.  Values from one month (account balances, cash on hand, etc.) are factored into the subsequent months to give a running total throughout the year, and an accurate picture of the annual budget status can be viewed by looking at the December tab.  I needed a formula for the daily expenditure allotment that would only decrement for the current month so that the values at the end of December would stay accurate.

         

        Thank you to everyone for your help.

Resources