Having the Date appear as absolute value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The formula below works to drop the date in the cells under a column showing the date done, but when I reopen the file on subsequent days, the date changes to the current date and does not remain the date I entered "done". Is there a way to have date drop into a cell when I enter "done" in C4 remain the date I entered "done" , and not change to the current date?

=IF(C4="Done",TODAY (),"")
 
Hi Jose

this can't be done without sme VBA code. Harlan grove posted a solution
some days ago (see below):
Enter his code in the workbook module. save and re-open your workbook.
After that you can use the defined name __NOW__ in your formula:
=IF(C4="Done",__NOW__,"")

HTH
Frank

and here is a copy of Harlan's posting
-----------------------------------------------
Same general idea I had, but I'd do it differently. 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
 
Jose,

If you add some code as suggested by Harlan Grove, you can then use

=IF(C4="Done",__NOW__,"")

Here is that code

Same general idea I had, but I'd do it differently. 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)

Jose in SF said:
The formula below works to drop the date in the cells under a column
Click to expand...
showing the date done, but when I reopen the file on subsequent days, the
date changes to the current date and does not remain the date I entered
"done". Is there a way to have date drop into a cell when I enter "done" in
C4 remain the date I entered "done" , and not change to the current date?
 
The formula below works to drop the date in the cells under a column
showing the date done, but when I reopen the file on subsequent days,
the date changes to the current date and does not remain the date I
entered "done". Is there a way to have date drop into a cell when I
enter "done" in C4 remain the date I entered "done" , and not change
to the current date?

=IF(C4="Done",TODAY (),"")
Click to expand...

A few people want to give me 'credit' for a technique that won't work in your
situation. Presumably you could use this workbook several times before C4
becomes "Done". The referenced technique would freeze the date you first opened
the workbook after having added the Open event handler, so probably not what you
want.

Would C4 ever change *from* "Done" to anything else? If so, the cell containing
your formula would again become blank? If so, you need a Calculate or
SheetCalculate event handler. Which cells would contain formulas like this?
 
Harlan,

You are right of course, but it can work if the OP only wants to know the
date it first becomes done by removing the If test and re-creating the name
each time. This way, the name is refreshed every time the workbook is
opened. As the OP only wants the date, that should be acceptable.

Bob
 
Bob Phillips said:
You are right of course, but it can work if the OP only wants to know the
date it first becomes done by removing the If test and re-creating the name
each time. This way, the name is refreshed every time the workbook is
opened. As the OP only wants the date, that should be acceptable.
Click to expand...
....

I'm not convinved the OP's formula was used *once*. If there are several
such formulas depending on several separate cells like C4, and if those
cells could independently evaluate to Done, a single define name won't work.

Without the OP following up, it's all idle guesswork.
 
Many thanks, the code appears to work at least in part. I unfortunately I didn't explain in detailed what I am trying to do

I currently have a worksheet where I list out a series of tasks to be "done" over a course of time. The task list is added to daily and the anticipated due dates recorded. The worksheet gives me networkdays to the due dates. If the task is not marked "done" (in column C) and passes the due date, the task is marked as "over due" and the number of days it is over dued is given. I created the VB module as you provided and saved it the workbook on 1/27/04 for several tasks completed that day, and it did place the date I marked the tasks done, and did not get replaced with the "current date" on subsequent days when I opened the workbook...joy of joys it worked. What I discovered when I entered "done" for several tasks completed today (1/30/04) on the appropriate row in Column C, in Column H where the date appears when I marked the specific tasks as done (in other words, today's date the 30th), 1/27/04 appeared. The long and short, I'm trying to capture the date a task is marked done and not have the workbook update the date each time I open the workbook to the "current date" or a "default date". Hope this makes sense


----- Harlan Grove wrote: ----

The formula below works to drop the date in the cells under a colum
showing the date done, but when I reopen the file on subsequent days
the date changes to the current date and does not remain the date
entered "done". Is there a way to have date drop into a cell when
enter "done" in C4 remain the date I entered "done" , and not chang
to the current date?
Click to expand...

A few people want to give me 'credit' for a technique that won't work in you
situation. Presumably you could use this workbook several times before C
becomes "Done". The referenced technique would freeze the date you first opene
the workbook after having added the Open event handler, so probably not what yo
want

Would C4 ever change *from* "Done" to anything else? If so, the cell containin
your formula would again become blank? If so, you need a Calculate o
SheetCalculate event handler. Which cells would contain formulas like this

-
To top-post is human, to bottom-post and snip is sublime
 
...
...
. . . What I discovered when I
entered "done" for several tasks completed today (1/30/04) on the appropriate
row in Column C, in Column H where the date appears when I marked the specific
tasks as done (in other words, today's date the 30th), 1/27/04 appeared. The
long and short, I'm trying to capture the date a task is marked done and not
have the workbook update the date each time I open the workbook to
the "current date" or a "default date". Hope this makes sense.
Click to expand...
...

It makes perfect sense, and is what I suspected originally.

The best way to do this is using a Change event handler specific to the
worksheet in which you'd be entering these "Done" values. You'd be entering
"Done" in column C and freezing the date at which you made that column C entry
in column H on the same row. I'll assume that if you deleted "Done" from any
cell in column C that you'd want the date in column H on the same row deleted as
well.

Copy the VBA code below. Right click on the tab for the worksheet in which you
make these entries, and click on View Code in the popup menu. Paste the VBA code
into the VBA sheet module.


'----- begin VBA -----
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const CHKCOL As Long = 3, COLOFS As Long = 5
Dim c As Range, h As Range

Set Target = Intersect(Target, Me.Columns(CHKCOL))
If Target Is Nothing Then Exit Sub

On Error GoTo CleanUp

Application.EnableEvents = False

For Each c In Target
Set h = c.Offset(0, COLOFS)
If c.Value = "Done" Then h.Value = Int(Now)
If c.Value = "" And IsDate(h.Value) Then h.ClearContents
Next c

CleanUp:
Application.EnableEvents = True
End Sub
'------ end VBA ------
 
Back
Top