+ Reply to Thread
Results 1 to 7 of 7

Set static date

  1. 10-08-2015, 07:26 AM #1
    mariur89
    mariur89 is offline
    Registered User
    Join Date
    11-24-2014
    Location
    norway
    MS-Off Ver
    2010
    Posts
    37

    Smile Set static date

    Dear excel pros

    Is it possible to automatically insert a static date if a cell contains a specific string?

    Example:
    Automatically insert a date in cell B1 when a task is 100% done (string "done" is written in cell C1).

    I would prefer a solution without using VBA
    Reply With QuoteRegister To Reply
  2. 10-08-2015, 07:53 AM #2
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: Set static date

    in B1
    =IF(C1="DONE",DATEVALUE("29/10/1961"),"")
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
    Reply With QuoteRegister To Reply
  3. 10-08-2015, 08:06 AM #3
    Aardigspook
    Aardigspook is offline
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Set static date

    If you're wanting a date-stamp to insert today's date but then have it not update later, format B1 as a date and use this formula:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    That will put today's date in B1 if C1 is 'done' and B1 is empty. If B1 already has a value (date or anything else) in it, it will stay the same. If C1 is later changed, the date in B1 will be blanked.

    Does that do what you want?
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.
    Reply With QuoteRegister To Reply
  4. 10-08-2015, 08:20 AM #4
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: Set static date

    Quote Originally Posted by Aardigspook View Post
    If you're wanting a date-stamp to insert today's date but then have it not update later, format B1 as a date and use this formula:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    I can't get that to work, I get a blank..and a circular reference warning.
    Reply With QuoteRegister To Reply
  5. 10-08-2015, 08:47 AM #5
    Aardigspook
    Aardigspook is offline
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Set static date

    Oops, forgot the critical step:

    For my formula in post 3 to work, you need to enable iterative calculations. go to File → Options → Formulas and select the box labelled 'Enable iterative calculation'. You can leave the 'Maximum Iterations' on 100 or reduce it to speed up calculations if you have a very large file.

    That will stop the warning about a circular reference, and allow the formula to work.
    Reply With QuoteRegister To Reply
  6. 10-09-2015, 03:27 AM #6
    mariur89
    mariur89 is offline
    Registered User
    Join Date
    11-24-2014
    Location
    norway
    MS-Off Ver
    2010
    Posts
    37

    Re: Set static date

    Quote Originally Posted by Aardigspook View Post
    Oops, forgot the critical step:

    For my formula in post 3 to work, you need to enable iterative calculations. go to File → Options → Formulas and select the box labelled 'Enable iterative calculation'. You can leave the 'Maximum Iterations' on 100 or reduce it to speed up calculations if you have a very large file.

    That will stop the warning about a circular reference, and allow the formula to work.
    Thank you so much Aardigspook! That worked very well
    Reply With QuoteRegister To Reply
  7. 10-09-2015, 06:27 AM #7
    Aardigspook
    Aardigspook is offline
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Set static date

    Glad to be of help - and thanks for the rep.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Highlight Rows Based on Date Range Using Static Date and Current Date
    By SaraStravers in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-23-2015, 07:38 PM
  2. Copying data between sheets with static and un-static cell references
    By peperammi69 in forum Excel General
    Replies: 2
    Last Post: 08-12-2013, 06:19 AM
  3. Static date
    By Pedro1803 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-13-2011, 12:41 PM
  4. Static left hand side column and static header row.. how?
    By glic in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-20-2007, 01:45 AM
  5. static date
    By venkateswararao in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-26-2007, 05:49 PM
  6. DATE STATIC
    By Shaggy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-21-2006, 09:20 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1