+ Reply to Thread
Results 1 to 8 of 8

Keep a date or replace it with today’s date

  1. #1
    Registered User
    Join Date
    08-23-2018
    Location
    Weston super Mare
    MS-Off Ver
    16.16
    Posts
    12

    Keep a date or replace it with today’s date

    I am a newbie to excel and don’t really know where to start with this formula.

    Basically I would like to do the following:

    If a1 is >= than b1 then leave c1 empty however if a1 is < Than b1 then put in today’s date in c1. However, what I want to achieve is that once the date has populated c1 I want that date to hold until a1 is >= than b1. The data for a1 is automatically refreshed every day and the number goes up and down.

    To give you a clearer picture of what I want to achieve:

    Column A = current share price
    Column B = highest share price
    Column C = date of highest share price

    If a share hits the highest price and then slowly declines over the following weeks I want to identify what date it hit its peak.
    Thank you in anticipation

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Keep a date or replace it with today’s date

    What you describe seems to me to be very much like a "time stamp". My first suggestion would be to use a strategy similar to: https://chandoo.org/wp/timestamps-excel-formula-help/ where you set up a "circular reference" that returns the current value of C1 as long as A1<B1. As soon as A1>=B1, then it will return the current date (using either the NOW() or TODAY() function). You must enable iteration in Excel's calculation options for this approach to work.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    08-23-2018
    Location
    Weston super Mare
    MS-Off Ver
    16.16
    Posts
    12

    Re: Keep a date or replace it with today’s date

    Hi Mrshorty thanks for the info I’ve got a better understanding of timestamps and circular formulas etc, but I am still struggling with dates. I’ve even replaced the date cell with text, and it still doesn’t work, this ‘not’ empty cell Formula <>’’ Has really got me baffled! I’m close to giving up and pouring myself a large brandy.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Keep a date or replace it with today’s date

    Not knowing what you have tried, it is difficult to say where you are going wrong. I would have expected something like =if(reset,"",IF(A1>=B1,TODAY(),C1)) where reset refers to a cell where you can enter 1 or TRUE when you want C1 to be empty.

  5. #5
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Keep a date or replace it with today’s date

    Maybe something like this in C1:
    =IF(A1>=B1,"",TODAY())

  6. #6
    Registered User
    Join Date
    08-23-2018
    Location
    Weston super Mare
    MS-Off Ver
    16.16
    Posts
    12

    Re: Keep a date or replace it with today’s date

    Hi MrShorty and Gregb11, thanks for your replies.
    Gregb11
    The formula you provide is the same as I originally had except I had <=. The problem I found is that if the share dropped 3 days in a row the date would be replaced with today's date. This is my main problem. I want the date the share hit its highest price, not todays date.
    MrShorty
    I tried your reset formula but didn't have iterations set properly so it came up with an error, and then the spreadsheet updated, so I couldn't check to see if it worked. I have attached a sample spreadsheet, please excusethe amateur formulas but as I said I am a newbie to excel.
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Keep a date or replace it with today’s date

    I did not see any attempt at the "reset formula" that I proposed, and my copy of Excel is not recognizing the _FV() function you are using in column C, so I am unable to debug your sample sheet. This seemed to work for me:

    1) Replace the _FV() formula in column C with =10*RAND() to get random numbers in this column. It should work the same with the _FV() function.
    2) I designate I1 as my "reset" cell and enter 1 into this cell.
    3) I edit the formula in D4 to be =IF($I$1,0,MAX(C4:D4))
    4) I edit the formula in E4 to be =IF($I$1,"",IF(D4<=C4,NOW(),E4)). I'm using the NOW() function so I can test over a time period of seconds to minutes rather than needing days to test it. I also need to format the cell to be hh:mm:ss so I can see time rather than date. It should all work the same with the TODAY() function.
    5) With 1 in I1, these formulas are returning 0 and "", so I enter 0/FALSE in I1 and the formulas will begin tracking the maxima.
    6) Repeatedly press F9 to generate new random numbers in column C and watch columns D and E to make sure they are tracking and time stamping the maximum values.

    Does that help?

  8. #8
    Registered User
    Join Date
    08-23-2018
    Location
    Weston super Mare
    MS-Off Ver
    16.16
    Posts
    12

    Re: Keep a date or replace it with today’s date

    Hi Mrshorty, you're a genius, it worked at treat thanks for spending your time resolving this. Much appreciated, from a happy Brit. Stay safe.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Establish range for # of days a case has been open via create date and today's date
    By excelingtoexcel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-06-2020, 11:54 AM
  2. Replies: 5
    Last Post: 12-01-2015, 02:36 PM
  3. [SOLVED] Sumif from date (Today or greater than today) to last date
    By thilag in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-23-2015, 03:53 AM
  4. [SOLVED] Formula to compare date (including month and year) from a listed date to today's date
    By mhewitson15 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-11-2014, 05:31 PM
  5. [SOLVED] Prefill text box in userform with today's date but allow user to enter unique date
    By moosetales in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-02-2014, 09:17 AM
  6. Replies: 6
    Last Post: 08-08-2011, 08:32 AM
  7. create a macro to alert me if today's date is within 5 days of expected delivery date
    By ashmcclure in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 09-25-2008, 05:51 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1