# Static Date Function



## getssmart (Nov 10, 2004)

I'm using Excell, I want to populate a cell with todays date, but I want the date value to remain static after that point i.e. If today is 4th May, when I post the date into the cell, when I re-open the workbook next week I still want it to say 4th May.

This therefore rules out the use of the [Today()] function, unless you know something I dont

This will form part of a calculation, so needs to be a function, not a manually inserted value.

Any Ideas how I can achieve this?

Thanks in advance.


----------



## DOTCOM (Jan 18, 2005)

Use = Date, From The Formulae Bar, It Will Then Open With Three Boxes, Year =2005, Month =05 And Date =07 (type It In). Then Click Ok.
This Date Will Then Remain Static On The Spreadsheet. (do Not Type In Date). This Can Be Used For Arguments As Long As You Set The Formats In The Cell Where The Formulae Is Used.



Hope This Helps



Dotcom


----------



## ReeKorl (Mar 25, 2005)

But if you want it automatically posted to the cell as part of a function then you will need to run a macro to do this. 

Let me look into some code I have and see if i can rustle up a little macro to do this for you.


----------



## ReeKorl (Mar 25, 2005)

OK, here comes the code.

Assuming you have no knowledge of VBA for Excel, here's how to use it.

1. Download the attachment (don't forget to scan it with your virus scanner as always)
2. Rename it to a '.BAS' extension
3. Open the Excel file and go into the Visual Basic Editor (found in Tools->Macro->Visual Basic Editor)
4. On the left you should find your workbook. It will probably be called 'VBAProject (yourworkbooknamehere)'
5. Right click on this and select 'Import...'
6. Change the file type to 'All Files" and browse to the .BAS file you just renamed and hit Open

This is now in your workbook and almost ready to run...

Double click on the module that has just been inserted and in the code window on the right you will need to change the name of the worksheet and which cell you want the date put into. I'm assuming you want the date in a British format as you're from here, but if you want it in American format, I've left instructions in the module also.

To run the macro, click Tools->Macro->Macros... and run InsertStaticDate.

Hope this helps. If anything doesn't make sense, post again and I'll try to clear it up.

-EDIT STARTS-
Forgot to mention, if you want it run as part of another VBA subroutine, then you can just add another line into that sub which says 'mdlInsertStaticDate.InsertStaticDate' and it will run.
-EDIT ENDS-


----------



## getssmart (Nov 10, 2004)

*Appologies*

Sorry I have not responded sooner, for some reason I am not getting notification of replies sent to me, so I thought no one had replied.

Thanks a bunch, I will have to study your replies to see if they apply to what I want...Just wanted to say thanks.

:4-dontkno oops I just found the setting to get email notifications at the foot of the page...Thought they were sent by default.


----------



## Housewins2 (Feb 22, 2009)

I have the same problem, except, I have cells in another sheet that when deleted, I want to insert a static date in the same cell on a different sheet.


----------



## codenameb (Feb 9, 2010)

thanks for the example reekorl!!


----------

