| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
if/then insert date but don't want automatic updates
I have a spreadsheet tracking personnel actions. I tried the following
formula =IF(L2="","",TODAY()) but I do not want the date stamp to automatically update when the spreadsheet opens. How can I write a simple formula so that when the cell has text entered into it, the date will be recorded in the adjacent cell as a static stamp? Thanks. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if/then insert date but don't want automatic updates
Long story made short... what you are asking for requires macros. If you want
to persue that it is a fairly simlpe macro but it many people don't wnat to go there... -- HTH... Jim Thomlinson "redleg" wrote: I have a spreadsheet tracking personnel actions. I tried the following formula =IF(L2="","",TODAY()) but I do not want the date stamp to automatically update when the spreadsheet opens. How can I write a simple formula so that when the cell has text entered into it, the date will be recorded in the adjacent cell as a static stamp? Thanks. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if/then insert date but don't want automatic updates
I am willing to try if you have an example I can tinker with. Thanks.
"Jim Thomlinson" wrote: Long story made short... what you are asking for requires macros. If you want to persue that it is a fairly simlpe macro but it many people don't wnat to go there... -- HTH... Jim Thomlinson "redleg" wrote: I have a spreadsheet tracking personnel actions. I tried the following formula =IF(L2="","",TODAY()) but I do not want the date stamp to automatically update when the spreadsheet opens. How can I write a simple formula so that when the cell has text entered into it, the date will be recorded in the adjacent cell as a static stamp? Thanks. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if/then insert date but don't want automatic updates
See this:
http://www.mcgimpsey.com/excel/timestamp.html -- Biff Microsoft Excel MVP "redleg" wrote in message ... I have a spreadsheet tracking personnel actions. I tried the following formula =IF(L2="","",TODAY()) but I do not want the date stamp to automatically update when the spreadsheet opens. How can I write a simple formula so that when the cell has text entered into it, the date will be recorded in the adjacent cell as a static stamp? Thanks. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if/then insert date but don't want automatic updates
As you have found out TODAY() is a volatile function that updates each day.
I would suggest some type of event code that adds a static timestamp rather than have a formula-derived one. But you can prevent a formula from updating using worksheet functions alone. Couple of methods on John McGimpsey's site One without VBA and one with. http://www.mcgimpsey.com/excel/timestamp.html Gord Dibben MS Excel MVP On Mon, 17 Aug 2009 10:33:01 -0700, redleg wrote: I have a spreadsheet tracking personnel actions. I tried the following formula =IF(L2="","",TODAY()) but I do not want the date stamp to automatically update when the spreadsheet opens. How can I write a simple formula so that when the cell has text entered into it, the date will be recorded in the adjacent cell as a static stamp? Thanks. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if/then insert date but don't want automatic updates
Start by recording a macro. Don't care what it is. When that is done, Select
Tools - Macro - Macros - Edit Add the following code Public Function StaticDate(ByVal Cell As Range) As Variant If Cell.Count 1 Then StaticDate = -1 Else StaticDate = Now() End If End Function Now go back to your spread sheet. Add the formula =StaticDate(A1) to the cell where you ant the date to appear (change A1 to the cell that triggers the date to appear). Now whenever cell A1 is changed the date will be changed in the cell where you added this formula. This is a regular formula that can be copied and pasted and... -- HTH... Jim Thomlinson "redleg" wrote: I am willing to try if you have an example I can tinker with. Thanks. "Jim Thomlinson" wrote: Long story made short... what you are asking for requires macros. If you want to persue that it is a fairly simlpe macro but it many people don't wnat to go there... -- HTH... Jim Thomlinson "redleg" wrote: I have a spreadsheet tracking personnel actions. I tried the following formula =IF(L2="","",TODAY()) but I do not want the date stamp to automatically update when the spreadsheet opens. How can I write a simple formula so that when the cell has text entered into it, the date will be recorded in the adjacent cell as a static stamp? Thanks. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if/then insert date but don't want automatic updates
Jim,
This macro worked for me to insert the date; however, I want to use it for multiple rows of data and ONLY if there is something in the adjacent cell. As I am inserting this reference, it inserts today's date, even if there is nothing in the adjacent cell. How do I fix that?? -- Kathryn "Jim Thomlinson" wrote: Start by recording a macro. Don't care what it is. When that is done, Select Tools - Macro - Macros - Edit Add the following code Public Function StaticDate(ByVal Cell As Range) As Variant If Cell.Count 1 Then StaticDate = -1 Else StaticDate = Now() End If End Function Now go back to your spread sheet. Add the formula =StaticDate(A1) to the cell where you ant the date to appear (change A1 to the cell that triggers the date to appear). Now whenever cell A1 is changed the date will be changed in the cell where you added this formula. This is a regular formula that can be copied and pasted and... -- HTH... Jim Thomlinson "redleg" wrote: I am willing to try if you have an example I can tinker with. Thanks. "Jim Thomlinson" wrote: Long story made short... what you are asking for requires macros. If you want to persue that it is a fairly simlpe macro but it many people don't wnat to go there... -- HTH... Jim Thomlinson "redleg" wrote: I have a spreadsheet tracking personnel actions. I tried the following formula =IF(L2="","",TODAY()) but I do not want the date stamp to automatically update when the spreadsheet opens. How can I write a simple formula so that when the cell has text entered into it, the date will be recorded in the adjacent cell as a static stamp? Thanks. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
if/then insert date but don't want automatic updates
=IF(A1="","",staticdate(A1))
If you don't want to use a formula you could use event code. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall If Target.Cells.Column = 1 Then N = Target.Row If Excel.Range("A" & N).Value < "" Then Excel.Range("B" & N).Value = Now End If End If enditall: End Sub Select your sheet tab and "View Code". Copy/paste the code into that sheet module. Edit to suit then hit Alt + q to return to Excel. As written the code will place a staic date/time in column B when data is entered in column A. Gord Dibben MS Excel MVP On Tue, 29 Sep 2009 12:56:01 -0700, Katlegal wrote: Jim, This macro worked for me to insert the date; however, I want to use it for multiple rows of data and ONLY if there is something in the adjacent cell. As I am inserting this reference, it inserts today's date, even if there is nothing in the adjacent cell. How do I fix that?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Automatic Date Insert | Excel Worksheet Functions | |||
| Automatic updates | Excel Discussion (Misc queries) | |||
| Sharing - Automatic Updates | Excel Discussion (Misc queries) | |||
| Insert Automatic, Non-Updating Date Stamp | Excel Discussion (Misc queries) | |||
| AUTOMATIC UPDATES | Excel Worksheet Functions | |||
Linear Mode
