Excel NOW() problem

[tvanharen]

I am currently making a sheet that requires only one input from the user per row, with the rest of the cells in that row automatically filled in when the user enters the input in this specific cell in each row. Otherwise, the cells are blank. I've achieved this for most of the cells I want to do this for, except for cells that record the day, month, date, and time of the user's input into this one specific cell per row. I originally thought the NOW() function would be perfect, and in my original implementation, I thought it did work. The problem is this: the next day when I entered a new input in the input cell, the previous cells that used the NOW() function all updated with the current time, date, month, day, year, etc instead of keeping the original data from when the user input was originally made. Below is the formula I used to display the day of the week of the user's input. Any help with maybe getting the cells to freeze after input or converting the cells to their values and ignoring the formula after input would be much appreciated. It's not essential, but I would like to have this functionality in it. OK, here's the formula:

=IF(NOT(ISBLANK(E58)),CHOOSE(WEEKDAY(NOW()),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"),"")

Essentially the formula works like this: the user input cell in this case is cell E58. If this cell is blank, then nothing appears in it. If the cell has been issued an input value, the CHOOSE() function gets an index value from WEEKDAY(NOW()) and finds the corresponding day of the week and returns it. Again, the problem is getting the cell to retain its value instead of constantly updating when more input is done elsewhere in the sheet. Thank you to all who consider my problem and devote time to it.

[Anne Troy]

I am looking for a resolution for getting the data entry date to be *hard data*....

As for your second question, you need only put, for instance, =E58 and then format the cell (not E58, but the destination cell) as Format-Cells, Custom, dddd
That is...unless I misunderstood your second question.

_________________
TheWordExpert

[ This Message was edited by: Dreamboat on 2002-04-07 21:13 ]

[Jay Petrulis]

Hi,

1. After the data is filled in, choose
Edit>Copy
Edit>Paste Special (values) in the cell

2. Suggested route:
Use a worksheet change event.

I am assuming that the column with your formula is column F.

1. Right click on the sheet tab and choose View Code

2. Copy the following and paste into the module.

----------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 And Len(Target) > 0 Then Target.Offset(0, 1) = Format(Date, "dddd")
If Len(Target) = 0 Then Target.Offset(0, 1) = ""
End Sub
----------------

Make sure you adjust to fit to your data.

I also added a check to see if a cell is cleared, the day column is also cleared. Please remove if not needed.

HTH,
Jay

On 2002-04-07 20:54, tvanharen wrote:
I am currently making a sheet that requires only one input from the user per row, with the rest of the cells in that row automatically filled in when the user enters the input in this specific cell in each row. Otherwise, the cells are blank. I've achieved this for most of the cells I want to do this for, except for cells that record the day, month, date, and time of the user's input into this one specific cell per row. I originally thought the NOW() function would be perfect, and in my original implementation, I thought it did work. The problem is this: the next day when I entered a new input in the input cell, the previous cells that used the NOW() function all updated with the current time, date, month, day, year, etc instead of keeping the original data from when the user input was originally made. Below is the formula I used to display the day of the week of the user's input. Any help with maybe getting the cells to freeze after input or converting the cells to their values and ignoring the formula after input would be much appreciated. It's not essential, but I would like to have this functionality in it. OK, here's the formula:

=IF(NOT(ISBLANK(E58)),CHOOSE(WEEKDAY(NOW()),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"),"")

Essentially the formula works like this: the user input cell in this case is cell E58. If this cell is blank, then nothing appears in it. If the cell has been issued an input value, the CHOOSE() function gets an index value from WEEKDAY(NOW()) and finds the corresponding day of the week and returns it. Again, the problem is getting the cell to retain its value instead of constantly updating when more input is done elsewhere in the sheet. Thank you to all who consider my problem and devote time to it.

[Anne Troy]

I've seen this question a lot. Jay, I hope you don't mind if I store this one away.



(2 minutes later)
Jay, is there any way to make it just put in the date and time now if, for instance you put a value into column A, and you want the date/time to auto-appear in column B (same row of course). In other words, without using the =NOW() in any cell?

_________________
TheWordExpert

[ This Message was edited by: Dreamboat on 2002-04-07 21:39 ]

[tvanharen]

Thanks guys...I'll give those a try.

[tvanharen]

Jay, the worksheet change event works great so far. I'll let you know if I can apply this effectively for the other elements that need it. Thanks for your help!

[tvanharen]

Ok, I keep getting a type mismatch error...guess I'll keep trying...

[Jay Petrulis]

On 2002-04-07 21:34, Dreamboat wrote:
I've seen this question a lot. Jay, I hope you don't mind if I store this one away.



(2 minutes later)
Jay, is there any way to make it just put in the date and time now if, for instance you put a value into column A, and you want the date/time to auto-appear in column B (same row of course). In other words, without using the =NOW() in any cell?

_________________
TheWordExpert

[ This Message was edited by: Dreamboat on 2002-04-07 21:39 ]

Hi Dreamboat,

You can use the VBA Now function to do what you wish.

Also, an interesting modification can be to put the date and time in a comment:

As before, but slightly modified:
-------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Len(Target) > 0 Then
Cells(Target.Row, "B") = Now
Cells(Target.Row, "B").AddComment (Format(Now, "m/d/yy h:mm"))
End If
If Len(Target) = 0 Then Target.Offset(0, 1) = ""
End Sub
-------------------------

Bye,
Jay

[Jay Petrulis]

On 2002-04-08 00:02, tvanharen wrote:
Ok, I keep getting a type mismatch error...guess I'll keep trying...

Hi,

Post more details so that we can eliminate your problem. This shouldn't be difficult to figure out.

Bye,
Jay

[Anne Troy]

Thanks, Jay!