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

Originally Posted by DC93

...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

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

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

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

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

Try changing the code to this:

Please Login or Register  to view this content.

BSB

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

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

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

You too.

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

BSB