Subtract a set value from a cell on a specific day and replace cell value

    I would like to learn how to create a formula that takes a Value in cell A1, and on the 15th of the month subtract 25 and place the value in Cell B1. If it is not the 15th of the month, then Cell A should reflect the Amount in Cell B.


    So if A1 is 1000, then on the 15th B1 will show 975 then on the 16th the value of A1 changes to 975.


    I tried using this formula =IF(DAY(TODAY())=15,A1-25,B1=A1) and it worked for changing the value of A1 from 1000 to 975. But when I saved and closed the excel document, and changed the time and date on my computer to show the 16th, then reopened the spreadsheet, the Value in cell A1 remained as 1000.


    And as I am writing this I have realized an added difficulty. As I am imputing the formula in, it may not be the 15th, so the information in A1 should only change after the amount has been deducted on the 15th only.

    Re: Subtract a set value from a cell on a specific day and replace cell value


    Your explanation is confusing because you are not telling us which cell a formula is in and you are not telling us if A1 is 1000 or a formula that results in 1000 and what that formula is.


    So if you could state what formulas are in what cells and whether A1 is 1000 or a formula that results in 1000 and what that formula is we could probably help you.


    Example:

    Quote

    So if A1 is 1000, then on the 15th B1 will show 975 then on the 16th the value of A1 changes to 975.


    This statement makes it sound like A1 has 1000 typed in it. If that is the case it can't be directly changed by a formula in another cell. The other cell can have a formula that references A1 and uses the value of A1 and does some math on it, but A1 has to remain 1000 since that is what is typed in A1.


    See my confusion?

    Bruce :cool:

    Re: Subtract a set value from a cell on a specific day and replace cell value


    I am very sorry for the Confusion. Cell A1 has 1000 entered into it. Cell B1 was where I was entering the formula.
    And yes, I see what you are saying about Cell A1 has to remain a constant since it was manually entered. I will re-evaluate what it is that I am trying to accomplish and find a more precise way of asking it.


    Thank you for pointing out my flaw. I apreciate it.

Participate now!

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

Register Yourself Login