+ Reply to Thread
Results 1 to 5 of 5

Freeze the date provided by today() function

  1. #1
    Registered User
    Join Date
    01-04-2013
    Location
    Faridabad, India
    MS-Off Ver
    Excel 2007
    Posts
    2

    Freeze the date provided by today() function

    Hello Everyone

    I am new to the forum and occasionally work on Excel to automate few of day to day work. I find this forum very rich in content and have solved lot of my problems in the past.

    So to start with, I will like to thank the members of this forum who are out there to help people like us.

    Now to explain my situation:

    1) I am in process of creating a meeting template for my team. We support a pretty huge Unix mid range environment.
    2) This meeting template is like a record keeper for managers to understand the biggest concern areas / issues in the support environment.
    3) I have a column, "Report Date" (Column D) and I have a column, "Aging" (Column H). Report date is self explaining, the day the issue was reported, it's a manual entry column. I have created one column (Column V), which is hidden in template to rest of the people, that has got the cell value as "=IF(B8="Open", TODAY())"
    4) The column B is a column with value, "Open" & "Close". So until the issue is in "Open" state, the hidden column will be true and will keep showing the current date
    5) The "Aging" column then have got this value "V4-D4" and driving it's value for the number of days the issue is open. So far, so good. Now find below my challenge

    Challenge I am facing:

    1) I want to add a new column as, "Date Completed"
    2) I want to automate the process of this value in "Date Completed" to be done automatically
    3) So the moment I mark, "Close" in Column B, the "Date Completed" cell in the row gets populated as per the current date and is freeze

    I hope I have been able to explain my problem. The reason I am doing this is that I want to see how much time we take as a team to resolve and address issues.

    Thanks all again for the past assistance I have received from this forum and thanks in anticipation for this challenge.

    Thanks & Regards
    Amit

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Freeze the date provided by today() function

    Hello mr.amit.ch and Welcome to the Forum,

    I think what you want can be done by VBA because the formula cannot provide a static date. Please take a look at the attached spreadsheet which contains code. By changing value in column "B" from Open to Close will automatically add current date in column "C"
    Attached Files Attached Files
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    01-04-2013
    Location
    Faridabad, India
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Freeze the date provided by today() function

    Thanks Alkey, I am going to try this and update here about the outcome. Really appreciate.

  4. #4
    Registered User
    Join Date
    06-01-2017
    Location
    Grand Rapids, MI
    MS-Off Ver
    8
    Posts
    1

    Re: Freeze the date provided by today() function

    Greetings,

    I do not yet know how to code using Visual Basic, I only use formulas at present. I have added the Developer tools to the ribbon but lost after that. Can someone share with me how to achieve the following through VB and then how to apply to my worksheet?

    PO issued?: Yes
    PO Issue Date: =IF(A2="Yes",TODAY(),"")

    Of course, the date changes every day versus remaining static (much written about that!). I need a one-time static date when "Yes" is picked.

    Thank you in advance.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Freeze the date provided by today() function

    KBC1 welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. update a value only TODAY, then FREEZE it
    By angelperez in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-16-2008, 08:30 AM
  2. [SOLVED] SUMIF within date range as a function of today()'s date
    By irvine79 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-06-2006, 01:00 PM
  3. MAX figure within a date range as a function of today()'s date
    By irvine79 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-06-2006, 12:45 PM
  4. =TODAY() function to fix the date
    By starguy in forum Excel General
    Replies: 4
    Last Post: 07-14-2006, 09:18 AM
  5. Today() or Date() function help
    By julisimo in forum Excel General
    Replies: 8
    Last Post: 01-03-2005, 01:23 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