Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

1 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
share|improve this answer
    
Thanks for your instant reply Lese. I just wanted to make the Checklist document simpler. So just wanted to know if there's some other way around... like using functions, etc. –  RODY ROY Mar 30 '12 at 12:11
    
@RODY ROY: Ah... if you're trying to do it via macros, then I believe .value should do what you want: openofficetips.com/blog/archives/2004/11/macros_getting.html –  Lèse majesté Mar 30 '12 at 12:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.