Static date 2

[AmritPaulSingh]

HI
I need to have the current date in one cell of an excel file depending on whether another cell is empty or not. I can do this with an if statement and copy the formula. The problem here is though that when I save the file and open it the next day, the date will update itself. I would like the date to stay the same for already entered entries.
Any suggestions.

 

[DaleWatson123321]

Amrit,

This routine will work...

Sub Set_Date()
If Range("test1&quot.Value = "" Then
Range("curdate&quot.Value = Now()
Else: Range("curdate&quot.Value = ""
End If
End Sub

I hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Zathras]

Since this isn't the VBA forum, here is a non-VBA way:

When you copy the formula, don't just paste. Use paste-special-values to lock in the current values instead of copying formulas.

 

[AmritPaulSingh]

Dale, I would like to get this done without using a macro.

Zathros, I would also like this to be automatically done..without having to copy past special.

Is there a formula that does...copies a value from a cell and not the reference.

Thanks for your help guys.

 

[Zathras]

Ok. If you really want to do this, here is a way involving circular references.

First. Set calculation to a fixed number of iterations to handle the circular reference problem: Tools/Options/Calculation check the "Iteration" box and set a reasonable number of iterations (6 or so. It depends on the complexity and nesting of cell references in other formulas)

Then set up the sheet like this (After it works, adjust to suit your taste.)

A1: 'Now
B1: 'Time of Entry
C1: 'Entry
A2: =NOW()
B2: =IF(C2="",$A$2,B2)

Set the format for B2 to be the date and/or time you want.

Set up conditional formatting for cell B2 as follows:
1. Select Format/Conditional Formatting... from the menu
2. Change Condition1 from "Cell Value Is" to "Formula Is"
3. Enter =(C2="&quot as the formula
4. Click Format...
5. Change the Color combo box selection to White
6. Click OK
7. Click OK

Copy the formula from B2 down the column as far as you need.

When you make an entry in column C, the time will appear and will be fixed. (As long as you never blank out the entry in column C.)

Let us know what you think. Personally, I would recommend using VBA. You can test for non-blank entries and set the date (if not already set) on the Worksheet_Change event. VBA isn't all that hard unless you are trying to do something really exotic.

 

[AmritPaulSingh]

Hi Zathras:
That's just what I needed. It works fine. Thanks.

I have another question though. Since I have the Circular reference checked (i.e. the iteration check box), would that change anything else in the worksheet. Hope it doesn't impact any other formulas I might be having.

Dale, Thanks to you too.

 

[Zathras]

Your other formulas should continue to work just fine. I did a little experimentation. You can set the iterations to 1 and everything should work ok.

Just remember, if a user ever blanks out the entry in column "C", the date will be reset the next time an entry is made there. (Keep good backups.)

 

[xlhelp]

Amrit,

If Zathras' solution helped you, you should acknowledge the efforts with a star. In addition to appreciating the contributor, it indicates to others in the Tek-Tip community that a satisfactory solution has been found. "Amicule, num is sum qui mentiar tibi?"