Make TODAY() stay the same date

Hi,

I have an Excel 2003 worksheet where when data is entered in a particular cell, the date is auto inserted into the first column. Essentially my code looks like this: =IF(F4="","",(TODAY())) When data is entered into F4 today's date is inserted into the first column.

The problem is that when I open the spreadsheet the next day, that day's date is now in the first column, however I want the automatically inserted date to remain static in the first column. I am trying to remove the need to type the date as part of a data entry set by having the formula in place however do not want it to change each time the sheet is opened up. I have searched the forum and also the Excel books which I have bought through this site and cannot find the answer I need.

Any help greatly appreciated.

Hi Danison,

I'm not sure I fully understand the scope of the problem, and I am by no means an Excel guru, but can you use copy & paste special values for the rows (or just that particular cell) that is filled in? Are you looking for a way to do this through a macro?

Hi Dylan,

Thanks for your reply. I thought what would be easier is to attach a copy of the spreadsheet I am referring to. It is an expense management tool. If you enter a number in column F (Invoice number), the date entered will show up in column A, and the month will show in column C (which is linked to the month serial number in the hidden column B). What I am wanting to do is to avoid having to enter the date and month data in columns A & C every time I want to enter information. I can then apply an autofilter to this spreadsheet to allow me to filter entries by month, by virtue of the 'month' text entry that auto inserts in column C. eg. I can look at all invoices for a brand for December only etc.

Problem is my formula in column A will always show the current date when the file is opened, rather than inserting the current date when an entry is made in column F, and then keeping that date static whenever the spreadsheet is next opened. I need to change the formula somehow in column A so that when the TODAY() date enters, it does not change every time I open the spreadsheet. Hope that makes sense.

Hi Palmetto,

Thanks for the advice. I do not pretend to be a guru when it comes to VBA etc, so did a bit of research to see how to insert code and have to humbly admit I tried it and for some reason the code has not entered into the cell. I highlighted my first data entry cell in column A, used Alt + F11 to open the VBA editor, entered my code and clicked Alt + F11 again, and exited out and the original formula is still in the cell. Am I doing something wrong to enter the VBA code in the cell?

Originally Posted by Palmetto

1. Manual entry typed or using short cut key (Ctrl + ; (semi-colon) for current date)

:up: Thanks for this tip, never knew about it and never would have even thought to go looking for a shortcut for this function. I'm going to try and use this a bunch over the next few days so that I don't forget, it is one of those things that should be very handy.

(I bolded the actual key combination in the quote, because when I first read this I didn't see the ";" and thought it was just "ctrl +"...which I just learned a couple of weeks ago adds in a new row or column and had been my favorite new shortcut until this came along)

Thanks Palmetto,

Have changed the formula and will check tomorrow and see if the date for today's entry remains static. Thanks for your help & happy new year!

Hi Palmetto,

I am not sure if I have done something wrong in attaching the code. I have left it a couple of days to see if test entries stay the same date as entered when I reopen however the dates are changing. For example, I did a couple of test entries yesterday and when I open today it is today's date for those respective entries (I am in Australia and today is 3rd Jan). Have I done something wrong in inserting this code?

Hi Roy,

Thanks for your advice. I have tried to use your formula and cannot seem to make it work. Please forgive the VBA new-boy questions!! I have attached a copy of the file with the code inserted, and as you can see I have altered the A1 to F1 in the formula (or whatever row is relevant) as this is the cell that data will be entered into which is meant to activate today's date in column A, and then leave that date static with the entry the next day when I open the spreadsheet. I am getting a NAME# error with one of the entries and also in F10 I entered some data today and the date has not shown up in column A. I presume it is something simple however I am slowly learning! Would appreciate your further advice.

Thanks Roy, I am still learning about VBA so thanks for your patience. I will do some more experimenting and see if I can make it work! Ben has also posted a response with code included which is great, so will try and work through both and learn 'on the job'!! Thanks again for your help.

Thanks Ben, much appreciated. I am not sure I understand what you have done however will work through it to see if I can figure it out! I am not sure what to do to use only one of the codes? Do I go into VBA and just delete the module? I am a bit out of my depth here!! Thanks heaps for your help - the screen shots are fantastic

Thanks, have gone in and deleted the code and left Roy's UDF - fingers crossed it will work! Thanks again. This forum is invaluable - thanks to people like yourself there to help learners like moi