+ Reply to Thread
Results 1 to 11 of 11

An updating date that doesn't change previous dates

  1. #1
    Registered User
    Join Date
    01-22-2016
    Location
    Leicester, England
    MS-Off Ver
    2013
    Posts
    12

    An updating date that doesn't change previous dates

    Hi there,

    I am trying to create a document where new field entries will be added everyday with differing amounts of new entries each day as it depends how many calls we receive from customers that we need to log.

    What I ideally would like is for today's date to automatically appear on a new field but for the previous dates not to update. I.e. using Today() gives me today's date which is good, however, when I then go in to add new entries tomorrow all of today's inputs dates will change to tomorrow's date.

    Does anybody know of a formula/formatting to make this happen? Basically a live update that doesn't change the previous fields dates?

    I have tried alternatives such as macros and the calendar input method but using macros isn't ideal to then communicate with my team. Ideally, some sort of formula to control this date process would be great.

    I hope that makes sense,

    Thanks
    DC

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,878

    Re: An updating date that doesn't change previous dates

    Quote Originally Posted by DC93 View Post
    ...but using macros isn't ideal to then communicate with my team.
    Does this mean VBA is out of the picture altogether?

    I think the easiest way would be to use a worksheet change event so if a value is entered in a specified column then a date is automatically entered in the corresponding row in the date column.

    This would however rely on using VBA.

    BSB

  3. #3
    Registered User
    Join Date
    01-22-2016
    Location
    Leicester, England
    MS-Off Ver
    2013
    Posts
    12

    Re: An updating date that doesn't change previous dates

    I just said that about macros because I included the calendar and then when I shared the document with a team member they couldn't use the macro, even though worksheet was saved as macro enabled etc.

    How would you go about creating the VBA for the worksheet change in this instance?

    Thanks a lot for your help.

    DC

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,878

    Re: An updating date that doesn't change previous dates

    Attached is a VERY quick and dirty example of what I mean.

    Impossible to tailor make it without seeing your workbook but it should point you in the right direction.

    BSB
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-22-2016
    Location
    Leicester, England
    MS-Off Ver
    2013
    Posts
    12

    Re: An updating date that doesn't change previous dates

    BSB, that works perfectly. Thanks a lot for sending over.


    On your example if you were then to delete the name the date still stays, is there something we can add onto the VBA coding to make it so that if the name field is then deleted that the date then disappears again?

    Thanks
    DC

  6. #6
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,878

    Re: An updating date that doesn't change previous dates

    I cannot see why not. I'm at work and about to run off to a quick meeting.
    I shall have a look at updating the code when I get back to my desk, unless someone else has solved it for you by then.

    BSB

  7. #7
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,878

    Re: An updating date that doesn't change previous dates

    Try changing the code to this:

    Please Login or Register  to view this content.
    BSB

  8. #8
    Registered User
    Join Date
    01-22-2016
    Location
    Leicester, England
    MS-Off Ver
    2013
    Posts
    12

    Re: An updating date that doesn't change previous dates

    You're a legend BSB, that has worked perfectly. After days of frustrating myself I'm very glad I came on here to ask - massive help!!

    Just to confirm when I go in tomorrow they will all still be saved as "22/01/2016" and then any I add tomorrow will come up as "23/01/2016"?

    Apologies for asking - I would change my computer clock to change the date and look myself but unfortunately as I am at work, I am restricted from being able to do so.

    Again, thanks so much for your help

    DC

  9. #9
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,878

    Re: An updating date that doesn't change previous dates

    Not a problem DC. Happy to help.

    And to answer your question, yes, they will remain as 22/01/2016 when you open the document tomorrow.

    You can test this easily by changing the code line that enters the date to:

    Please Login or Register  to view this content.
    Then enter some names in column B. You will see that the time changes with each you input, but it doesn't affect previous ones.

    BSB

  10. #10
    Registered User
    Join Date
    01-22-2016
    Location
    Leicester, England
    MS-Off Ver
    2013
    Posts
    12

    Re: An updating date that doesn't change previous dates

    Just tried it, and yes that is spot on what I was after and it works perfectly.

    Thanks so much!

    Have a good day!

    DC

  11. #11
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,878

    Re: An updating date that doesn't change previous dates

    You too.

    Don't forget to mark the thread as SOLVED if you're happy you have a working solution.

    BSB

+ 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. Cells that Populates by date & doesn't delete previous days
    By DasBurgers in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-18-2014, 01:39 PM
  2. [SOLVED] 2 Different Dates but chose the previous date
    By Jarvin24 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-31-2014, 10:51 PM
  3. Date stamp that doesn't change.
    By Nitefox in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 14
    Last Post: 09-23-2013, 03:54 PM
  4. Replies: 0
    Last Post: 05-30-2013, 07:50 PM
  5. Change Weekend Date To The Previous Friday
    By MarkMcCann in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-30-2012, 09:45 AM
  6. Add Current Date but when i save it, date doesn't change when reopened
    By gotbitey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-21-2011, 06:00 PM
  7. Replies: 3
    Last Post: 02-09-2006, 10:30 AM

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