Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
date stamp
Shawn,
This came up recentlyu, and the best soluition IOMO was this one from Harlan Grove. Obviously, you can name it whatever you want, it doesn'[t have to be __NOW__ ============================================= I'd have the Open event add the workbook-level name __NOW__ with the date/time value when the workbook was first opened by a user (rather than the developer, who would need to leave __NOW__ undefined or initialized to #N/A). Then the name __NOW__ could be used anywhere in any formula in the workbook. Private Sub Workbook_Open() On Error GoTo CleanUp Application.EnableEvents = False If IsError(Evaluate("__NOW__")) Then _ Me.Names.Add Name:="__NOW__", RefersTo:=Now CleanUp: Application.EnableEvents = True End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "shawn modersohn" wrote in message ... I've got =IF(Inventory_list!C2="y", Inventory_list!A2, 'formulas etc'!$D$9) on a master sold sheet. d9 refers to a blank cell. I've also got on the master sold sheet, =IF(A2<0, 'formulas etc'!$D$14) for a date column. d14 refers to today's date. The d14 today's date is fine as the initial input to a cell, but I want to datestamp the dates on the master sold sheet so they do not update. Actually, I want to do this to the whole column so as dates get entered periodically, they do not change from the inital value. What do I need to do and thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
date stamp
Shawn,
Problem with this function is that it can recalculate, which would mean that your date is updated. You need one that doesn't. To answer the previous question here's how to enter the code. To enter it, on the sheet tab, right-click and select View Code. In the VB IDE in the project pane on the left for the workbook you are working on, select ThisWorkbook from the list of Microsoft Excel objects (it has an Excel icon beside it) by double-clicking to open the code module. Enter the code. You then use it in a formula like this =IF(A2<0, __NOW__) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "shawn modersohn" wrote in message ... Jackpot, I found something that works. Function DateAndTime() DateAndTime = Now End Function with =IF(A2<0,DateAndTime()) "shawn modersohn" wrote in message ... Perhaps I should have explained that I am just progressing to advanced excel techniques and what you just said has gone a bit over my head. I was hoping for a cut and paste job but I might as well ask you to break it down for me. How exactly does this work? Do I just open the vb editor type this in? I have a basic understanding of names, instead of A1, I could refer to a cell as Item if it were so named. Does the name in this case refer to a formula? Also, unless an entry is located in the item column, the date column states "false", which is necessary for the =IF(A2<0, 'formulas etc'!$D$14) function to work. Does this change any considerations to your solution. Thank you very much. "Bob Phillips" wrote in message ... Shawn, This came up recentlyu, and the best soluition IOMO was this one from Harlan Grove. Obviously, you can name it whatever you want, it doesn'[t have to be __NOW__ ============================================= I'd have the Open event add the workbook-level name __NOW__ with the date/time value when the workbook was first opened by a user (rather than the developer, who would need to leave __NOW__ undefined or initialized to #N/A). Then the name __NOW__ could be used anywhere in any formula in the workbook. Private Sub Workbook_Open() On Error GoTo CleanUp Application.EnableEvents = False If IsError(Evaluate("__NOW__")) Then _ Me.Names.Add Name:="__NOW__", RefersTo:=Now CleanUp: Application.EnableEvents = True End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "shawn modersohn" wrote in message ... I've got =IF(Inventory_list!C2="y", Inventory_list!A2, 'formulas etc'!$D$9) on a master sold sheet. d9 refers to a blank cell. I've also got on the master sold sheet, =IF(A2<0, 'formulas etc'!$D$14) for a date column. d14 refers to today's date. The d14 today's date is fine as the initial input to a cell, but I want to datestamp the dates on the master sold sheet so they do not update. Actually, I want to do this to the whole column so as dates get entered periodically, they do not change from the inital value. What do I need to do and thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
date stamp
How about fixing your clock so you are not posting from the future.
-- Regards, Tom Ogilvy shawn modersohn wrote in message ... Jackpot, I found something that works. Function DateAndTime() DateAndTime = Now End Function with =IF(A2<0,DateAndTime()) "shawn modersohn" wrote in message ... Perhaps I should have explained that I am just progressing to advanced excel techniques and what you just said has gone a bit over my head. I was hoping for a cut and paste job but I might as well ask you to break it down for me. How exactly does this work? Do I just open the vb editor type this in? I have a basic understanding of names, instead of A1, I could refer to a cell as Item if it were so named. Does the name in this case refer to a formula? Also, unless an entry is located in the item column, the date column states "false", which is necessary for the =IF(A2<0, 'formulas etc'!$D$14) function to work. Does this change any considerations to your solution. Thank you very much. "Bob Phillips" wrote in message ... Shawn, This came up recentlyu, and the best soluition IOMO was this one from Harlan Grove. Obviously, you can name it whatever you want, it doesn'[t have to be __NOW__ ============================================= I'd have the Open event add the workbook-level name __NOW__ with the date/time value when the workbook was first opened by a user (rather than the developer, who would need to leave __NOW__ undefined or initialized to #N/A). Then the name __NOW__ could be used anywhere in any formula in the workbook. Private Sub Workbook_Open() On Error GoTo CleanUp Application.EnableEvents = False If IsError(Evaluate("__NOW__")) Then _ Me.Names.Add Name:="__NOW__", RefersTo:=Now CleanUp: Application.EnableEvents = True End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "shawn modersohn" wrote in message ... I've got =IF(Inventory_list!C2="y", Inventory_list!A2, 'formulas etc'!$D$9) on a master sold sheet. d9 refers to a blank cell. I've also got on the master sold sheet, =IF(A2<0, 'formulas etc'!$D$14) for a date column. d14 refers to today's date. The d14 today's date is fine as the initial input to a cell, but I want to datestamp the dates on the master sold sheet so they do not update. Actually, I want to do this to the whole column so as dates get entered periodically, they do not change from the inital value. What do I need to do and thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
date stamp
Shawn - we've all done that before.. I know I have ;)
"shawn modersohn" wrote in message ... Thanks for all the help, I really appreciate it. The clock is a result of me trying to see what gets updated in the workbook, I just forget to set it back before posting. "Tom Ogilvy" wrote in message ... How about fixing your clock so you are not posting from the future. -- Regards, Tom Ogilvy shawn modersohn wrote in message ... Jackpot, I found something that works. Function DateAndTime() DateAndTime = Now End Function with =IF(A2<0,DateAndTime()) "shawn modersohn" wrote in message ... Perhaps I should have explained that I am just progressing to advanced excel techniques and what you just said has gone a bit over my head. I was hoping for a cut and paste job but I might as well ask you to break it down for me. How exactly does this work? Do I just open the vb editor type this in? I have a basic understanding of names, instead of A1, I could refer to a cell as Item if it were so named. Does the name in this case refer to a formula? Also, unless an entry is located in the item column, the date column states "false", which is necessary for the =IF(A2<0, 'formulas etc'!$D$14) function to work. Does this change any considerations to your solution. Thank you very much. "Bob Phillips" wrote in message ... Shawn, This came up recentlyu, and the best soluition IOMO was this one from Harlan Grove. Obviously, you can name it whatever you want, it doesn'[t have to be __NOW__ ============================================= I'd have the Open event add the workbook-level name __NOW__ with the date/time value when the workbook was first opened by a user (rather than the developer, who would need to leave __NOW__ undefined or initialized to #N/A). Then the name __NOW__ could be used anywhere in any formula in the workbook. Private Sub Workbook_Open() On Error GoTo CleanUp Application.EnableEvents = False If IsError(Evaluate("__NOW__")) Then _ Me.Names.Add Name:="__NOW__", RefersTo:=Now CleanUp: Application.EnableEvents = True End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "shawn modersohn" wrote in message ... I've got =IF(Inventory_list!C2="y", Inventory_list!A2, 'formulas etc'!$D$9) on a master sold sheet. d9 refers to a blank cell. I've also got on the master sold sheet, =IF(A2<0, 'formulas etc'!$D$14) for a date column. d14 refers to today's date. The d14 today's date is fine as the initial input to a cell, but I want to datestamp the dates on the master sold sheet so they do not update. Actually, I want to do this to the whole column so as dates get entered periodically, they do not change from the inital value. What do I need to do and thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
date stamp
You were absolutely right. Thanks for the help, I got it figured out. If I
pay attention here I just might learn something. "Bob Phillips" wrote in message ... Shawn, Problem with this function is that it can recalculate, which would mean that your date is updated. You need one that doesn't. To answer the previous question here's how to enter the code. To enter it, on the sheet tab, right-click and select View Code. In the VB IDE in the project pane on the left for the workbook you are working on, select ThisWorkbook from the list of Microsoft Excel objects (it has an Excel icon beside it) by double-clicking to open the code module. Enter the code. You then use it in a formula like this =IF(A2<0, __NOW__) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "shawn modersohn" wrote in message ... Jackpot, I found something that works. Function DateAndTime() DateAndTime = Now End Function with =IF(A2<0,DateAndTime()) "shawn modersohn" wrote in message ... Perhaps I should have explained that I am just progressing to advanced excel techniques and what you just said has gone a bit over my head. I was hoping for a cut and paste job but I might as well ask you to break it down for me. How exactly does this work? Do I just open the vb editor type this in? I have a basic understanding of names, instead of A1, I could refer to a cell as Item if it were so named. Does the name in this case refer to a formula? Also, unless an entry is located in the item column, the date column states "false", which is necessary for the =IF(A2<0, 'formulas etc'!$D$14) function to work. Does this change any considerations to your solution. Thank you very much. "Bob Phillips" wrote in message ... Shawn, This came up recentlyu, and the best soluition IOMO was this one from Harlan Grove. Obviously, you can name it whatever you want, it doesn'[t have to be __NOW__ ============================================= I'd have the Open event add the workbook-level name __NOW__ with the date/time value when the workbook was first opened by a user (rather than the developer, who would need to leave __NOW__ undefined or initialized to #N/A). Then the name __NOW__ could be used anywhere in any formula in the workbook. Private Sub Workbook_Open() On Error GoTo CleanUp Application.EnableEvents = False If IsError(Evaluate("__NOW__")) Then _ Me.Names.Add Name:="__NOW__", RefersTo:=Now CleanUp: Application.EnableEvents = True End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "shawn modersohn" wrote in message ... I've got =IF(Inventory_list!C2="y", Inventory_list!A2, 'formulas etc'!$D$9) on a master sold sheet. d9 refers to a blank cell. I've also got on the master sold sheet, =IF(A2<0, 'formulas etc'!$D$14) for a date column. d14 refers to today's date. The d14 today's date is fine as the initial input to a cell, but I want to datestamp the dates on the master sold sheet so they do not update. Actually, I want to do this to the whole column so as dates get entered periodically, they do not change from the inital value. What do I need to do and thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
date stamp
I've got =IF(Inventory_list!C2="y", Inventory_list!A2, 'formulas etc'!$D$9)
on a master sold sheet. d9 refers to a blank cell. I've also got on the master sold sheet, =IF(A2<0, 'formulas etc'!$D$14) for a date column. d14 refers to today's date. The d14 today's date is fine as the initial input to a cell, but I want to datestamp the dates on the master sold sheet so they do not update. Actually, I want to do this to the whole column so as dates get entered periodically, they do not change from the inital value. What do I need to do and thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
date stamp
Perhaps I should have explained that I am just progressing to advanced excel
techniques and what you just said has gone a bit over my head. I was hoping for a cut and paste job but I might as well ask you to break it down for me. How exactly does this work? Do I just open the vb editor type this in? I have a basic understanding of names, instead of A1, I could refer to a cell as Item if it were so named. Does the name in this case refer to a formula? Also, unless an entry is located in the item column, the date column states "false", which is necessary for the =IF(A2<0, 'formulas etc'!$D$14) function to work. Does this change any considerations to your solution. Thank you very much. "Bob Phillips" wrote in message ... Shawn, This came up recentlyu, and the best soluition IOMO was this one from Harlan Grove. Obviously, you can name it whatever you want, it doesn'[t have to be __NOW__ ============================================= I'd have the Open event add the workbook-level name __NOW__ with the date/time value when the workbook was first opened by a user (rather than the developer, who would need to leave __NOW__ undefined or initialized to #N/A). Then the name __NOW__ could be used anywhere in any formula in the workbook. Private Sub Workbook_Open() On Error GoTo CleanUp Application.EnableEvents = False If IsError(Evaluate("__NOW__")) Then _ Me.Names.Add Name:="__NOW__", RefersTo:=Now CleanUp: Application.EnableEvents = True End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "shawn modersohn" wrote in message ... I've got =IF(Inventory_list!C2="y", Inventory_list!A2, 'formulas etc'!$D$9) on a master sold sheet. d9 refers to a blank cell. I've also got on the master sold sheet, =IF(A2<0, 'formulas etc'!$D$14) for a date column. d14 refers to today's date. The d14 today's date is fine as the initial input to a cell, but I want to datestamp the dates on the master sold sheet so they do not update. Actually, I want to do this to the whole column so as dates get entered periodically, they do not change from the inital value. What do I need to do and thanks. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
date stamp
Jackpot, I found something that works.
Function DateAndTime() DateAndTime = Now End Function with =IF(A2<0,DateAndTime()) "shawn modersohn" wrote in message ... Perhaps I should have explained that I am just progressing to advanced excel techniques and what you just said has gone a bit over my head. I was hoping for a cut and paste job but I might as well ask you to break it down for me. How exactly does this work? Do I just open the vb editor type this in? I have a basic understanding of names, instead of A1, I could refer to a cell as Item if it were so named. Does the name in this case refer to a formula? Also, unless an entry is located in the item column, the date column states "false", which is necessary for the =IF(A2<0, 'formulas etc'!$D$14) function to work. Does this change any considerations to your solution. Thank you very much. "Bob Phillips" wrote in message ... Shawn, This came up recentlyu, and the best soluition IOMO was this one from Harlan Grove. Obviously, you can name it whatever you want, it doesn'[t have to be __NOW__ ============================================= I'd have the Open event add the workbook-level name __NOW__ with the date/time value when the workbook was first opened by a user (rather than the developer, who would need to leave __NOW__ undefined or initialized to #N/A). Then the name __NOW__ could be used anywhere in any formula in the workbook. Private Sub Workbook_Open() On Error GoTo CleanUp Application.EnableEvents = False If IsError(Evaluate("__NOW__")) Then _ Me.Names.Add Name:="__NOW__", RefersTo:=Now CleanUp: Application.EnableEvents = True End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "shawn modersohn" wrote in message ... I've got =IF(Inventory_list!C2="y", Inventory_list!A2, 'formulas etc'!$D$9) on a master sold sheet. d9 refers to a blank cell. I've also got on the master sold sheet, =IF(A2<0, 'formulas etc'!$D$14) for a date column. d14 refers to today's date. The d14 today's date is fine as the initial input to a cell, but I want to datestamp the dates on the master sold sheet so they do not update. Actually, I want to do this to the whole column so as dates get entered periodically, they do not change from the inital value. What do I need to do and thanks. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
date stamp
Thanks for all the help, I really appreciate it. The clock is a result of
me trying to see what gets updated in the workbook, I just forget to set it back before posting. "Tom Ogilvy" wrote in message ... How about fixing your clock so you are not posting from the future. -- Regards, Tom Ogilvy shawn modersohn wrote in message ... Jackpot, I found something that works. Function DateAndTime() DateAndTime = Now End Function with =IF(A2<0,DateAndTime()) "shawn modersohn" wrote in message ... Perhaps I should have explained that I am just progressing to advanced excel techniques and what you just said has gone a bit over my head. I was hoping for a cut and paste job but I might as well ask you to break it down for me. How exactly does this work? Do I just open the vb editor type this in? I have a basic understanding of names, instead of A1, I could refer to a cell as Item if it were so named. Does the name in this case refer to a formula? Also, unless an entry is located in the item column, the date column states "false", which is necessary for the =IF(A2<0, 'formulas etc'!$D$14) function to work. Does this change any considerations to your solution. Thank you very much. "Bob Phillips" wrote in message ... Shawn, This came up recentlyu, and the best soluition IOMO was this one from Harlan Grove. Obviously, you can name it whatever you want, it doesn'[t have to be __NOW__ ============================================= I'd have the Open event add the workbook-level name __NOW__ with the date/time value when the workbook was first opened by a user (rather than the developer, who would need to leave __NOW__ undefined or initialized to #N/A). Then the name __NOW__ could be used anywhere in any formula in the workbook. Private Sub Workbook_Open() On Error GoTo CleanUp Application.EnableEvents = False If IsError(Evaluate("__NOW__")) Then _ Me.Names.Add Name:="__NOW__", RefersTo:=Now CleanUp: Application.EnableEvents = True End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "shawn modersohn" wrote in message ... I've got =IF(Inventory_list!C2="y", Inventory_list!A2, 'formulas etc'!$D$9) on a master sold sheet. d9 refers to a blank cell. I've also got on the master sold sheet, =IF(A2<0, 'formulas etc'!$D$14) for a date column. d14 refers to today's date. The d14 today's date is fine as the initial input to a cell, but I want to datestamp the dates on the master sold sheet so they do not update. Actually, I want to do this to the whole column so as dates get entered periodically, they do not change from the inital value. What do I need to do and thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating date from a Date & Time stamp | Excel Discussion (Misc queries) | |||
Create a button that will date stamp todays date in a cell | Excel Discussion (Misc queries) | |||
Date stamp spreadsheet in excel to remind me of completion date | Excel Worksheet Functions | |||
Date Stamp | Excel Discussion (Misc queries) | |||
date stamp | Excel Discussion (Misc queries) |