Today() does not stay should lock,but change when open the next day

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    First off, I'm running Excel 2013.
    Second, I have reset my iterative calculation to MAX Iterations to 1 and Max Change to 0.


    My formula is: =IF(AND(A2>=A4,C3=""),TODAY(),"")


    What I am saying above is, when A2 >=A4 insert todays date in C3, otherwise stay blank. If there is a date already, do not change.


    This is not working for me.


    What it does, it stays blank like it should. But once the date is inserted it still changes the next day. Also, once the date is inserted when you click on the save icon it toggle from date to blank to date to blank.


    What am I doing wrong here?


    Thanks in advance


    rooky1

    Re: Today() does not stay should lock,but change when open the next day


    I don't know what cell this formula is in, it almost sounds like you are saying it's in C3.


    Code
    =IF(AND(A2>=A4,C3=""),TODAY(),"")


    You are using the and function which means both conditions have to be true for the and function to return true.


    So your formula says A2 has to be greater than or equal to A4 and C3 has to be blank, if those are both true, then today is put into the cell with the formula, otherwise the cell with the formula will be blank.

    Bruce :cool:

    Re: Today() does not stay should lock,but change when open the next day


    Quote

    But once the date is inserted it still changes the next day.


    The TODAY() function is a volatile function, it's return value is calculated and updated whenever the spreadsheet is calculated. If you want the value to stay as the date that you used it, you will have to overwrite it with the literal value.

    Re: Today() does not stay should lock,but change when open the next day


    Yes, that is true. Problem is trying to prevent TODAY() from updating the next day.

    Re: Today() does not stay should lock,but change when open the next day


    Yes, that is my problem. How would I "overwrite" it with a literal value? I do not understand how to apply this value.
    BTW Guys, I am unable to use a macro (company does not allow it).


    Thanks to all for the help.


    rooky1

Participate now!

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

Register Yourself Login