Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
display date of today in cell X when number 0 in cell Y
Hello, I am using excel 2007 and I am figuring out a way to display
the date of today (and 'capture' it) in one cell Y when there is a number (0) entered in an other cell X. The thing is that, when I open the excel sheet tomorrow, the date displayed in Y still has to be the date of today and not have changed in tomorrow's date. Background : the purpose of the xls sheet is to create a sort of logbook with entry's of file numbers and their entry/close dates. ....Sorry if this turns out to be a silly question : I'm just began to learn Excel, so every help on my learning path is more than appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
display date of today in cell X when number 0 in cell Y
hi,
i don't know if you know VBA, because for do what you must use an event code, for the following code, i suppose that initially there's nothing in the cell Y and i suppose that the cell's address X is "A1" and the cell's address Y is "B1" you have to paste the following code into ThisWorkbook and adapt range address, save and close the Workbook the next time the Workbook will open, the code "Workbook_Open" will be execute Private Sub Workbook_Open() If Range("A1") 1 And Range("B1") = "" Then Range("B1") = Now: Range("B1").NumberFormat = "m/d/yyyy" End Sub -- isabelle |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
display date of today in cell X when number 0 in cell Y
On Jun 2, 12:57*pm, anneleen machiels
wrote: I am using excel 2007 and I am figuring out a way to display the date of today (and 'capture' it) in one cell*Y when there is a number (0) entered in an other cell X. The thing is that, when I open the excel sheet tomorrow, the date displayed in Y still has to be the date of today and not have changed in tomorrow's date. Congrats! You are lightyears ahead of the nebbishes who use TODAY() without thinking of the consequences "tomorrow". The way you state the requirements, I believe it is impossible; or I don't understand. If X10 is true today, usually it will be true tomorrow initially when you open the file (unless.... TBD). So if we provide a simple way to capture today's date when X10 is true today, it will likely capture tomorrow's date when X10 is true tomorrow. I wonder if your requirement really is: capture the date when X1 is changed such that it becomes 0. And I will add: remove the date when X1 is changed such that it is no longer 0. The low-tech solution is to simply press ctrl+; in the cell where you want the date when you want to snapshot today's date. That is, while pressing Ctrl, press semicolon, then release both. But it sounds like you want something more automatic. AFAIK, that requires a Worksheet_Change event macro. Right-click the worksheet tab at bottom and click View Code. That should open a VBE window with a large pane on the right. Click the lefthand pull-down menu where it might say "(General)" and select Worksheet. Unfortunately, that automagically includes an unneeded event macro. Do not delete it now. We will return to it later. Position the cursor outside that macro, copy the following event macro, and paste into the VBE editing pane. '---------- begin copy below here Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range, r As Range Set r = Intersect(Target, Range("a:a")) If Not r Is Nothing Then Application.EnableEvents = False For Each c In r If IsNumeric(c) Then With c.Offset(0, 1) If c 0 Then .Value = Date .NumberFormat = "m/dd/yyyy" Else .ClearContents End If End With End If Next Application.EnableEvents = True End If End Sub '---------- end copy above here __Now__ you can delete the text for the unneeded event macro. Note: The above design makes several assumptions that you might need to change. First, it assumes the values to test for 0 (called X1 above) is the entire column A. Second, it assumes that you want to put today's date into the corresponding cell in column B. If that is not the case, you need to change Range("a:a") and/or Offset(0,1) accordingly. Finally, the code assumes that the desired date format is m/dd/yyyy. If not, you need to change that accordingly. Unfortunately, we cannot write simply "Date", at least not in VBA with XL2003. Before you save the Excel file, be sure to set macro security appropriately. I like Medium level because it __always__ gives me the opportunity to disable as well as enable macros. But arguably, that is inconvenient. Sorry about the excessive burden for someone who is just learning Excel. You are asking for a fairly advance behavior, or so it seems. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populate Cell with another cell, but only if the date equals today | Excel Worksheet Functions | |||
IF TODAY equals date in cell A10, or if TODAY is beyond that date | Excel Worksheet Functions | |||
I want to copy a cell by date if Cell = Today() then... | Excel Discussion (Misc queries) | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
Turn cell red if today is greater or equal to date in cell | New Users to Excel |