Excel NOW() problem
Excel NOW() problem
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Excel NOW() problem

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.


  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  4. #4
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  8. #8
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks, Jay!
    ~Anne Troy

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com