Excel NOW() problem

tvanharen

New Member
Joined
Apr 6, 2002
Messages
6
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top