Solved: Automatically Update date in cell value if workbook is saved under a different name.

[vanhunk]

Good day,

When I save the workbook as a new revision, I want to automatically change the date in a cell to the current date (i.e. the date the revision was changed). In other words I want this to happen automatically during the saving action.

The file name is "PSIM Model Tool Rev..." where ... represents the version. I have a formula in the spreadsheet that extracts the ... part of the file name.
The formula is:

 =SUBSTITUTE((MID(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1),21,LEN(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1))-24))," ",".")

The formula returns for example: "1.94", when the file is saved as "PSIM Model Tool Rev 1 94.xls".

A date is given in another cell as, for example: "01-Oct-12", if I now save the file as "PSIM Model Tool Rev 1 95.xls", the date in this cell must automatically update to today's date, i.e. to "05-Oct-2012", but stays as "01-Oct-12" when saved only (i.e. when the name of the file stays the same).

Thank you

[snb]

I'd prefer a UDF:

[VBA]
Function snb()
snb = Format(ThisWorkbook.BuiltinDocumentProperties(12), "dd-mm-yyyy")
End Function
[/VBA]

in e.g. cell C1

=snb() 

[vanhunk]

Tnx snb,
I don't think it will work in my case, it must only change when the name of the file is changed, not when it is saved otherwise.

[snb]

You have my permission to adapt it to your needs.

[vanhunk]

Thanks, If I only knew how.

[snb]

Maybe not that complicated; let's make a line of reasoning (that should always precede coding):

If you want the last saved date only to be 'changed' after renaming the workbook, it's necessary to store the workbook's name, independent of it's actual name.
After changing the name, the last saved date/time can be refreshed by comparing the stored name to the actual name.
After that the new name has to be stored as well.

[vanhunk]

That I understand, what I can not do is use the "new" filename before it is saved as such. The change only takes effect after it has already been saved. In other words it has to be recalculated and saved again to get the required effect.

[vanhunk]

Is there no way of doing this? I will be very disappointed!

Tnx

[vanhunk]

I have found a solution, although very weird.

The problem I had is that doing it the way everyone suggested left the saved file not updated, unless the file is saved again, which I would not like to depend on. This could be fine on its own, but what I wanted to achieve is to have a date value in say, cell D3 which will update only when the file is saved under a new revision. I wanted to link it to the updating of the value of the formula. If I did as everyone suggested, this date value will only update when the file is opened up again and thus give the wrong date as date of revision, and still rely on saving the file again.

I found a solution:
What I have discovered is, that as long as there is a formula, any formula, somewhere in the workbook that has a result of "#REF!", formulas that use "cell("filename",A1)" anywhere in the formula will be updated before the file is saved under a new name, while saving. It is actually quite useful. It is also made what I tried to achieve possible. No other suggestion so far achieved the same thing.

Thanks for all the contributions.