Technique: How to Input Current Time on Demand

[billycar]

How do I get the Spreadsheet part to input the Time (and Date) into a Cell, when the user pushes a button or other construct for that Cell?

To Clarify What I am After:

I want to end up with Start and End Times for a Task, and have it calculate elapsed minutes. I don't want a Current Date/Time that gets auto-recalculated ... only when I push a button over a cell should this occur for that cell.

Then in another column, I should get a Time Elapsed calculation, maybe only when both Start and End Time Cells have been used.

In the final analysis, I want to use this data to track my progress in executing my Tasks for each day of the week.

[jgd]

I'm not sure if it's exactly what you want, but you might have a look at this thread on the Support forum.

Jacqueline

[billycar]

Merci.

I am currently experimenting with Basic Functions:

Function FixDateTime as Date
FixDateTime = Now ()
End Function

But, I just noticed every time I open my file, it puts in the current time wherever I reference this function.

Your reference looks interesting ... thanks.

[billycar]

Using the link info above, I derived the following macro.

To TimeStamp an individual cell: select a sample cell you want to insert the current time and Record a Macro:

Choose Tools > Macros > Record macro

1 ) Type into the Formula Bar: =TEXT(NOW(); "hh:mm:ss")
2 ) Choose Edit > Copy
3 ) Choose Edit > Paste Special...
4 ) Uncheck the boxes until only the "Strings" choice remains checked.
5 ) Click the "OK" button.

Go to the Macro window and click "Stop Recording".

Name the macro. e.g. "TimeStamp"

Now Add TimeStamp as a Menu Item and/or give it a Keystroke.

Then whenever you want to insert the current time into a cell, have the cell formatted correctly, select it, and run the macro from the Menu or using your Keystroke.

To finish solving the original problem above, using the TimeStamp Macro:

To calculate Time Elapsed between s Start Time (G1) and Stop Time (H1) Columns, have the following formula in the receiving Elapsed Time Column:

= IF(H1>G1; H1-G1;"")

Now TimeStamp H1 ... then TimeStamp G1 ...

Works Great!

[jgd]

Seems that the new article listed here in NeoWiki needs to be written , with your enhancement.

Jacqueline

[billycar]

I'll consider that in awhile ... now, I have to get back to rehearsing with my new Spreadsheet Music Practice Planner by NeoOffice. After a week, I hope to give myself some Graphs of where I've spent my time ... and where I need to move to in my studies of Piano/Flugelhorn Jazz Theory and Practice.

One modification to the algorithm above: add to 1) " ... and hit Accept". May be intuitive, but needs to be programmed in, as part of the Macro.

Could also add notes on use of DateStamp as another example.

Perhaps there's a more direct way of converting formats, but I think we're going from a Function formatted Cell, to a Text formatted one to keep it from changing every time NOW() is updated, and the "Copy/Paste Special" technique suggested in the other link is appropriate.

Thanks for NeoOffice and this Forum.

[sardisson]

[jgd]

Or course, Smokey is right

I did not want to ask billycar or someone else to write the article. I might try to do it myself in a couple of days. I just wanted to say it's a good trick, and it's the second time (at least) that this question has been posted here.

Jacqueline

[billycar]

Taken as a compliment.

Oh ... the algorithm breaks down if the Cell one is stamping, is a merged cell.

It leaves the Function in the Formula / Cell window after giving an error message ... still works, though, going through the Copy/Paste Special, if in an unmerged cell.

Best wishes.