+ Reply to Thread
Results 1 to 7 of 7

I'd like to have one cell display the current date each time another cell is edited.

  1. #1
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010 14.04.4760.1000
    Posts
    439

    I'd like to have one cell display the current date each time another cell is edited.

    Simply I'd like a cell, say A1, to display today's date each time B1 is edited. Simply problem to state but I suspect it might not be so easy to accomplish. Hopefully not. let me know. Thanks
    Excel 1.0.1 (16.0.14326.20140) (Android)
    Excel 2010 14.04.4760.1000

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: I'd like to have one cell display the current date each time another cell is edited.

    Hi,

    You'll need a sheet change macro for this.

    i.e.
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010 14.04.4760.1000
    Posts
    439

    Re: I'd like to have one cell display the current date each time another cell is edited.

    oh darn. I'm not familiar with macros and macros don't get read in excel for android so I don't think that will work for me. Thanks anyway. Unfortunately I'm limited to the limits of excel mobile.

  4. #4
    Forum Contributor
    Join Date
    01-05-2017
    Location
    New York
    MS-Off Ver
    Office 2016
    Posts
    206

    Re: I'd like to have one cell display the current date each time another cell is edited.

    Put in cell A1 =NOW(). This will update every time the sheet recalculates, so not sure if that works for you.

    If you want it formatted as mm/dd/yyyy without the current time also use this: =TEXT(NOW(),"mm/dd/yyyy")

  5. #5
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010 14.04.4760.1000
    Posts
    439

    Re: I'd like to have one cell display the current date each time another cell is edited.

    Thanks but I don't think so. It would need to be just every time the other cell is edited/changed.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: I'd like to have one cell display the current date each time another cell is edited.

    the only way that will happen is with VBA. The only other thing closest to that would be =TODAY() - NOW() is not necessary, that adds the time as well - but as said, that is volatile and will update each day
    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

  7. #7
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: I'd like to have one cell display the current date each time another cell is edited.

    Here's how to put a date stamp in a cell whenever another cell is updated, without VBA.
    It comes from this site but there's some sort of display problem with the formulae there which makes the instructions a bit hard to follow, so I've collated them below, plus I've amended the formulae and method slightly to make it a bit easier to follow the calculations and to meet your request for a date-stamp rather than a time-stamp.

    Enable Iterative Calculations
    This method uses circular references, so you need to enable iterative calculations. Go to the Office button (File button in 2010), click 'Options' then 'Formulas.' At the top, in the 'Calculation Options' section, you'll see an option for 'Enable iterative calculation' - tick/select/check it. The default figures underneath are '100' and '0.001', which you can leave alone. (The site linked above gives a good explanation of what this does and why it's needed, which I'm not going to repeat here.)

    Named Ranges
    Now, back on your workbook, let's create some Named Ranges, to make it a bit easier to follow the references in the formulae you're about to create. This also means you can move the cells around without having to update the cell references all the time, which is handy.
    Named Range 1: You said you want to check when B1 is updated, so let's name that as 'NR_Data' - to name a range, select the cell(s) then click in the cell reference box to the left of the formula box (where it says 'B1' at the moment) and just type in the Name.
    (As an aside, it's good practice to preface all your Names with NR or something like that, so that you can easily spot them in your formulae - it also allows you to call a range something like NR_CAB12 whereas CAB12 itself wouldn't be allowed as it's a valid cell reference.)
    Named Range 2: Pick any other cell. This will provide a count when the NR_Data cell is changed. So let's Name it 'NR_DataCount'.
    Named Range 3: Pick any other cell. This will provide the date when the count in the NR_DataCount cell changes. So we'll Name it 'NR_CountDate'.
    Named Range 4: Cell A1, which will give you the final date you want (the date the NR_Data cell was changed) so we'll Name it 'NR_DataDate'.

    Formulae
    In the NR_DataCount cell, put in this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This will return an integer, a hyphen and a copy of the data in the NR_Data cell (for example, '1 - blahblahblah'). The integer will start at '1' and increment to 9, then 0, then 1 again (always remaining a single digit).
    The logic behind this is:
    If NR_Data is blank, return a blank.
    If it's not blank, there's three possible situations:
    (1) this is the first data entered - that is, NR_DataCount is blank - so return '1 - NR_Data'
    (2) the data hasn't changed - that is the right-hand section of NR_DataCount is equal to what's in NR_Data - so return whatever NR_DataCount is already (don't change it)
    (3) the data has changed, so increment the first digit by 1 and update with the current contents of NR_Data.
    (For anyone analysing the formula, the ""&NR_Data in the 'LEN(NR_DataCount)-4)=""&NR_Data,' section is to force NR_Data to be evaluated as text.)


    In the NR_CountDate cell, put in this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This will return an integer (the same as in NR_DataCount), hyphen and the date. The date will just be a number but don't worry about that for just now.
    If you'd rather have a time-stamp than a date-stamp, then change the two instances of TODAY() to NOW().
    The logic behind this formula is very similar to the previous formula, except that we can tell the difference between situations (2) and (3) based on whether the integer in this cell matches the integer in NR_DataCount.

    In the NR_DataDate cell (A1), put in this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This will return the date. Format the cell as a date so it doesn't show as just a number.

    Possible problems
    If you amend either of the formulae in NR_DataCount or NR_CountDate while there's data in NR_Data, you usually get #VALUE errors appearing. I think this is due to the circular references, which gets Excel confused when combined with the IF statements. If this happens, just delete whatever's in NR_Data and when you enter new data the formulae will work again.
    Also, sometimes when you enter data for the first time into the NR_Data cell, the final NR_DataDate cell stays blank. I have NO idea why this happens but it's easy to fix - just select the cell, press F2 then Return (this re-enters the formula without changing anything).


    I've attached a file with the above working.
    Hope this all does what you want.
    Attached Files Attached Files
    Regards,
    Aardigspook

    Recently moved house, internationally, during COVID (!) 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 to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 7
    Last Post: 06-22-2016, 10:26 PM
  2. Replies: 2
    Last Post: 09-16-2014, 07:59 AM
  3. Display current time based on cell changes.
    By shiva_reshs in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-19-2013, 12:16 AM
  4. Show date/time of when cell/column was last edited?
    By Bwangster12 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-24-2013, 03:01 AM
  5. Populate cell with current date and time, then lock cell
    By panny in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-17-2012, 06:13 PM
  6. Insert automatic date and time stamp in a cell if any cell of the same row is edited
    By kdhanerawala in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 11-12-2012, 06:18 PM
  7. [SOLVED] Display Current Date With Specific Formatting In Cell Only Once
    By swordswinger710 in forum Excel Programming / VBA / Macros
    Replies: 39
    Last Post: 03-02-2012, 12:49 PM

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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.06538 seconds
  • Memory Usage 9,092KB
  • Queries Executed 16 (?)
More Information
Template Usage (35):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)bbcode_code
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (7)memberaction_dropdown
  • (1)navbar
  • (5)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (7)postbit_legacy
  • (7)postbit_onlinestatus
  • (7)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • bbcode_parse_start
  • postbit_imicons
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • postbit_attachment
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1