Save date in cell on sheet modification

[Onesock]

Hi Everyone

I have a costing spreadsheet with the date in cell A:1.
If I change any cell within that spreadsheet I need the date in A:1 to change automatically to the new date.
Please help with a formula that I can insert in A:1 that can do this.

I have read up about timestamps but cant seem to find the solution for me.

Thanks in advance

Onesock

[jeffreybrown]

In A1 >> =NOW()

Anytime anything on the sheet happens, A1 will recalculate to the new date.

[Onesock]

Hi Jeff

That works fine ,but is not altogether what Im needing.
Let me try explain again.
In cell A1 the date and time is recorded( time is not really needed in my case , but helps with testing your solution).
If for instance on 20/10/2012 I first created a costing.
On 21/10/2012 I return to that same spreadsheet to check on something.
I open it and the date in cell A1 should read 20/10/2012 ( I now know when last that sheet was modified).
If I happen to change any cell within the spreadsheet - and save ( perhaps a price,quantity, etc ) , I need A1 to automatically update to the current date.
If I open that spreadsheet a few days later I will be able to then tell when last it was modified.

Hope this gives a better and clearer understanding of my challenge.

Regards

Onesock

[jeffreybrown]

In the sheet, place this code which will only fire when the sheet calculates.

Format the cell for time

Please Login or Register  to view this content.

[Legend Rubber]

Maybe try this?

Please Login or Register  to view this content.

Change the ("A1:F25") to whatever your workbook peramaters are....

[Legend Rubber]

and you can format cell A1 to whatever you like...year month day with or without time...

[Legend Rubber]

if this solves your post, remember to mark solved, and also click the star below my name.

[Onesock]

Hi Jeff

I have inserted the code in a new module ( Alt F11 ) > insert module > copy/paste code > ( Alt +Q) > save as .xlsm.
But im not winning . What could I be doing wrong.

Again - thanks

Onesock

[Onesock]

Hi Jeff

Works !! Is there a way that the code could be written that the date/time only modifies if the save tab is clicked.

Onesock

[Onesock]

Hi Jeff
On this challenge - is there maybe a formula that will work instead.

Onesock

[Legend Rubber]

To do it before it saves use...

Please Login or Register  to view this content.

As far as a formula... what code did you insert of Jeff's code? i dont see anything but the "=NOW()"?

[Onesock]

OOOPS Sorry.

I missed that I was talking to Legend Rubber - Please forgive
But if you could please see above and respond I would appreciate it

Onesock

[Onesock]

Hi Legend Rubber

Where do I put that code. Is it part of your original code ? or is it a new module.
Please advise.

ps. Jeffs code was :

Please Login or Register  to view this content.

Thanks

Onesock

[Legend Rubber]

Enter this...

Please Login or Register  to view this content.

Put this in "AltF11>ThisWorkbook...
I have attached...
Book1.xlsxSavetest.xlsm

[Onesock]

Hi Rubber Legend

Thanks for your patience.
I have inserted your code : ie POST #5
CELL A1 has the formula =NOW().
But each time I change a cell ( just by clicking in/out that cell ) the time(seconds) change.
I need it to only change if I "SAVE"

Your code in POST#11 - not sure what to do with that code ( Is it part of your original code or must it be a seperate entry or ?? )

Then if possible - if all this coding can be dropped, and perhaps instead a formula is placed in A1 that does what Ive been trying to explain

I would prefer a formula as I need to insert the solution into 100's of files.Saving all files as .xlsm could/will take a long time.

Thanks again

Onesock

[Onesock]

Hi Rubber Legend

please dont give up on me !!

Onesock

[jeffreybrown]

You have somewhat lost me. At first you wanted the date to change when a change was made on the sheet and now you are talking about before a save. What do the two have to do with each other?

If you do "before save", every time you hit save you will get a new date. I thought when you open the file but did not make a change then you don't want the date updated. You only want the date changed when a modification takes place.

Please think this through and clearly explain what you are after. Not sure it is a formula you want due to as I explained earlier, Now() will fire every time you open the workbook.

[Onesock]

Hi Jeff

Please forgive me for the confusion.
I will try and explain again.

a) I have 100's of individual costing files all with their inception date in cell A1 ( NO formula )
b) The costing consists of different variables . ie: material cost , labour , overhead ,etc
c) As the year progress , so do my costs change
d) So sometimes I return to a particular spreadsheet to check on pricing.
e) I do 'click' around to see what I have done previously
f) Sometimes I make changes to a cell , just to see what the implication would be to the total.
g) If Im happy with the new total I will 'save' that change - if not I will just exit the spreadsheet.
h) So my QUESTION - If I make a change anywhere on the spreadsheet - and 'save' - then I need the date (A1) to automatically update to the current date.
i) If I dont 'save' then no change to the date (A1).
j) It would suit me better if I could insert a formula in A1 that would do this , but macros would also work.
k) I find with macros it might take me a really long time to make the changes to all the files .
l) Also with macros everytime I open the file it has the security window

Hope this explains a bit better than before.
And again thanks for everyones patience

Regards

Onesock

[Legend Rubber]

No problem... take out the "=NOW()" in A1, you don't need it...
Click AltF11, you should see...
- VBAProject (yourbookname)
- Microsoft Excel Objects
Sheet1 (yoursheetname)
Sheet2 (yoursheetname)
Sheet3 (yoursheetname)
ThisWorkbook
In ThisWorkbook put the following code...

Please Login or Register  to view this content.

Change A1 to whatever cell you need...
That solves this part...
I will try to figure out a formula, but i am not sure if that will be possible...
If i have helped, click the little star under my name...

[Legend Rubber]

this will change to the current date every time you click save...
so if you dont want to update the date and time, dont press save...
Also, sorry about leaving you hanging last night, but i had to go...
Hopefully my previous post helped...
I still have had now luck wiht a formula.

[Onesock]

Hi Rubber Legend
I too had a crisis to attend to .So sorry for the delay.
I have entered the code - and voila it works great ----- BUT
After I close the spreadsheet and then return to it some time later - make changes etc and then save . OOOPs doesnt work anymore.
Any suggestions.
Also I have 100's of files and this way will take me forever - so yip Im still after a formula.

I DO appreciate you efforts so far - THANKS

Onesock

[Legend Rubber]

Book1.xlsxSavetest.xlsm
This works fine for me, and i closed and saved it 10 times...

[Onesock]

Hi Rubber Legend
Nope not working for me .
Im using xcel 2003 . Does that make difference.

Onesock

[Onesock]

OOOOps sorry

Legend Rubber - NOT Rubber Legend

[Legend Rubber]

Sorry, i dont have any way to test in 2003...
So it works the first time you zave, but no other times?
or it has never worked again since the first time?

[Onesock]

Hi
Works fine after inputting the code . But as soon as I close the sheet and reopen - it doesnt work anymore

[Legend Rubber]

can you attach your spreadsheet?
are you sure you have it in the right spot?

[Legend Rubber]

i am going, but will be back at 5am and will try to look at it if you send it...

[Onesock]

Hi Legend
Your sample file also doesnt work on my pc.
So back to the fORMULA option ???