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
Philip West's avatar
Philip West
Steel Contributor
Sep 16, 2018

I think this works for you..

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

 

MONTH expects a date to check, you cant say if month(8) you have to say if month(a date)=8

 

This formula is checking things like this: if todays month = 8, perform your 30-today*100. Else if todays month is less than 8 the result is 0, else the result is 3000.

  • 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
      MVP
      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.

Resources