A time and Date Formula

[sinspawn56]

Hello.
Here is what I want to do.
I have a centralized document that myself and my co-workers use. I would like to insert a date formula that upon opening the "Main" document displays the current date (that is no problem). The catch is, when myself or my co-workers make a change to that document, and "save as" I would like that date to stick (no longer display the current date, but the date in which it was "saved as").

Ideas?

[c.vaibhav]

You might have displayed the date using"=Now()".

While saving the document you can copy the cell in which the date is displayed and Paste the value of that cell using Pastespecial property..

Regards,
Vaibhav

[sinspawn56]

I have contemplated converting the formula to its value, but I am trying to make it most simple for everyone. Some co-workers forget to do that, so I was trying to make it a no hassle type of thing.

[ChemistB]

Hmmm, do you always "Save As" thus the original document is more of a template? If so, we can put simple code in the workbook that will convert that cell formula (=TODAY()) to it's value. However, if someone resaves the template, you'll lose your formula.

Edit: if you save your original file as a xlt (template), then there's less danger of people overwriting it.

[c.vaibhav]

there could be other ways to do so.. but right now the most simplest one that comes to my mind is by using Macro..

you can use the following code..

Suppose the name of the sheet in which the date is stored is "abc"
In VBA-->Microsoft Excel Objects-->ThisWorkbook, write the following code

Please Login or Register  to view this content.

[shg]

c.vaibhav,

Please take a few minutes to read the forum rules, and then edit your post to add code tags.

[sinspawn56]

Hmmm, do you always "Save As" thus the original document is more of a template? If so, we can put simple code in the workbook that will convert that cell formula (=TODAY()) to it's value. However, if someone resaves the template, you'll lose your formula.

Edit: if you save your original file as a xlt (template), then there's less danger of people overwriting it.

I have not saved the document as a Template (xlt) but more or less it is a template. It is not to be overwritten only saved as.

[ChemistB]

You can paste this code (similar to c.vaibhav's) into the Workbook object of the VBA Editor. This is written assuming your "=TODAY()" formula is in sheet1 cell A1

Please Login or Register  to view this content.

Does this work for you?

[sinspawn56]

I found the workbook object but how do I go about pasting your code in there?


EDIT* I found the object and pasted the code. But I am unable to save the template with the formula in there. I am having to go back into the template and redo the formula.

[ChemistB]

The easiest way without having to go through manually opening up the VBA editor is to right click on the small Excel icon in the upper left of your spreadsheet next to the File dropdown menu. One of the options should be "View Code" Clicking on that will automatically put you into the "This workbook" code window in VBA. How's that?

[sinspawn56]

Ok, if the code is running before I save the document, how do I save the code into the workbook without it running and converting the formula to its value?

[ChemistB]

In order to make changes to the template, open it without enabling macros. So add your code, save the template, reopen the workbook but do not enable macros, then enter your formula again and resave it. Did that work?

[sinspawn56]

Yes, that works. Thank you both for all your help.