How to copy the value from a cell to another (using a formula), but not formula from the source cell

Question

I'm making use of NOW() function at many places in my spreadsheet, which is basically a checklist document.

So my requirement is that for each step, it should record the datetime at which that step was completed. But whenever the Now() is being used... all the previously updated cells withe datetime are also getting updated again. Hence at the end of the day, am not able to get the detailed time at which each of those steps are completed.

This is how I've created as of now:

• Used a Checkbox for each step. Once the step is complete, on clicking the Checkbox, it will update the cell A1 as "Completed".

• In the cell B1, I've put the following function which will check for the value in A1. If it is "Completed", then it will update the value of B1 with the current datetime using Now()

  =IF(A1="Completed";NOW();"")
  

Please let me know a solution for this. Also if there any other alternative.

Answer 1

This is something I often have to do, but the only options I know of are less than ideal:

1. You can copy the cell as normal, but use "Paste Special" and uncheck all except for "Number" or whatever it is that you want to paste.

2. Or, you can "copy-and-paste by proxy", i.e. copy and paste into a regular textbox (notepad, the address bar of your favorite browser, an open shell window, whatever is most convenient). Then copy that text and paste it into the cell.

I usually choose the second method because it's actually a lot quicker (because you can use keyboard shortcuts) than "Paste Special".

Edit:
I misunderstood the question. If you're looking for a way to do it programmatically, it is possible. You just need to use the .value property of the cell.

I've tested this in LibreOffice Calc, and it works. I suspect it'd also work in OpenOffice Calc, as it's adapted from that OpenOffice snippet I linked to in the comments:

Sub GetDate
    Dim myTable as Object, mySheet as Object, _
        curDate as Date, curTime as Date
    Const TRRange = "A2:F2"  'Template row range
    Const ColPosDate = 0     'Date column position
    Const ColPosTime = 5     'Time column position

    mySheet = ThisComponent.Sheets(0)
    myTable = mySheet.getCellRangeByName(TRRange)

    'curDate and curTime should be identical as they're both just NOW() cells
    ' --one formatted (for display) as date and the other as time
    curDate = myTable.getCellByPosition(ColPosDate,0).value
    curTime = myTable.getCellByPosition(ColPosTime,0).value

    print curDate
End Sub