Excel "Today()"

  recap 09:07 25 Oct 2004

I have created a spreadsheet that requires that date to be input automatically. I thought by using the Today() function it would work. When I opened the spreadsheet todays date was inserted but the previous date (yesterdays)changed to todays.

The spreadsheet will have over 1000 entries in total that will be input over a six month period. The date is important to know for correct record keeping.

How can I keep the previous date from changing to the current date?

Without appearing to be facetious, why not enter the date where it should be? It really is a matter of day/month as the year will be automatically added.

So the 25th October is entered as 25/10 the cell will display the result that you have formatted the cell to be and should it be a custom format of

dddd”, the” dd”th of” mmmm yyyy

your entry of 25/10 will become –

Monday, the 25th of October 2004

"input automatically"

Sorry Recap, missed this bit of your post, I thought you were just being lazy :))))

Still the reply might help somebody to use custom formats!

Best wishes

  recap 10:13 25 Oct 2004

Thanks no need to be sorry, as you say your post may help somebody in the future.

kind regards

I will leave it to you where the following code could be used but it rests on the fact that cell A1 contains the formula =today(). The B1 in the code is where you want the date of the transaction to appear and the PasteSpecial just copies the value of A1 and will not change, depending on your automation code. Contact me via the envelope if you wish




Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


  VoG II 11:06 25 Oct 2004

Type in


Press F9

Press Enter

Format as date.

  recap 11:51 25 Oct 2004

VoG, is F9 the short way of automating the process.

  VoG II 12:01 25 Oct 2004

If you type in


and press Enter it will enter a formula in the cell that will update every time the sheet calculates (including when the file is opened). Hence your observation that the date changes.

If you press F9 before pressing Enter, Excel will replace the formula with the value. This will not change when the sheet recalculates.

  recap 12:03 25 Oct 2004

Thanks VoG.

Have you got the answer or do you require the process to be fully automated.

If you require full automation then I believe there can be no other answer than a coded solution.

If the automation is part of the entry process or a part of the worksheet opening process then the next available row needs to be known and I assume that the column would be known.

In either event the following code could be useful to you, (or others) in translating the today() function into a value and placing it in the next available row. It assumes that the formula =TODAY() is in A1 of the active sheet.


LastRow = Activesheet.Cells(Rows.Count, iCol).End(xlUp).Row + 1

Cells(LastRow, KnownColumn) = Cells(1, 1).Value


Hope I am not clouding the issue.


For iCol put in the date column

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Best of the Grad Shows 2017: University of the West of England (UWE)

Best value Mac: Which is the best £1249 Mac to buy

Les meilleures GoPro 2017