Time/Stamp on value change

krijes · Post by **krijes** » Thu Jul 15, 2010 9:11 pm

Cell A1= done Cell B1= IF(A1="";"";IF(B1="";NOW();B1))

what I want is, if I modify the cell A1 then it should update cell B1; presently its happening only when I delete the content from cell A1 and then updating it. ( it should happen without deleting the cell; i.e- on instance of modifying also it should update time stamp)

hope i am clear

No Macro pls.

Regards
krijes

Post by **FJCC** » Thu Jul 15, 2010 9:34 pm

Does this work?

Code: Select all

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

krijes · Post by **krijes** » Thu Jul 15, 2010 10:25 pm

FJCC wrote:Does this work?
Code: Select all
=IF(ISBLANK(A1);"";NOW())

Nope it doesn't;
thought it update the time stamp without deleting the cell content; BUT it update the time stamp on a entry anywhere in the sheet, pls find the attachment to test yourself.

Regards
krijes

time_stamp.ods: (10.94 KiB) Downloaded 382 times

krijes · Post by **krijes** » Sat Jul 17, 2010 4:09 pm

someone pls help; waitig for a solution.. : (

Post by **Zizi64** » Sat Jul 17, 2010 5:17 pm

No Macro pls.

Why not?

Post by **MrProgrammer** » Sat Jul 17, 2010 8:56 pm

I wanted to do this sort of thing myself a few weeks ago. But after thinking about how formulas work, I came to the conclusion that one cannot do this with Calc formulas. There are at least two problems.

krijes wrote:Cell B1= IF(A1="";"";IF(B1="";NOW();B1))

This will not work because cell B1 references itself. I was unable to determine an alternate form of this sort of formula which bypasses the self-referrential problem. A second problem is that NOW is a dynamic function whose value changes. It will be recalculated, at minimum, each time the file is saved and opened. You can convert the function into a number (which won't change) using Edit, Copy, Edit, Paste Special, uncheck Formulas, OK, however that is an extra step.

A macro could presumably determine the current date/time and insert it as a value into a cell, but you have ruled that option out.

Calc is a complicated product, and it's possible that this could be accomplished with formulas alone using techniques that I haven't considered, however I think this is unlikely.

Post by **Charlie Young** » Sat Jul 17, 2010 9:35 pm

I came to the same conclusion as Mr. Programmer as far as doing it with formulas alone. I have written some macros that accomplish the task, so I might as well post them here since no one has to use them if they don't want to. There are problems here concerning the behavior of removeModifyListener, and note that the attached spreadsheet should be treated with caution, as I did experience a few crashes while working on it. Please do not have any valuable unsaved work open at the same time you are experimenting with this.

The attached spreadsheet attaches modify listeners to each cell in Sheet1.A1:A1000 upon file open, and removes them on file close. It seems necessary to set up an array of listeners so as to have a different one for each cell. If the same listener is attached to multiple cells, removeModifyListener only works on event.source.

A modification to column A sets the time in the corresponding row of column B. I have formatted column B to MM/DD HH:MM:SS, but this is unessential, and any desired date/time/number format could be applied.

Again, treat this with caution.

aloarjr810 · Post by **aloarjr810** » Sat Jul 17, 2010 10:20 pm

MrProgrammer wrote:There are at least two problems.
Cell B1= IF(A1="";"";IF(B1="";NOW();B1))
This will not work because cell B1 references itself. I was unable to determine an alternate form of this sort of formula which bypasses the self-referrential problem. A second problem is that NOW is a dynamic function whose value changes. It will be recalculated, at minimum, each time the file is saved and opened. You can convert the function into a number (which won't change) using Edit, Copy, Edit, Paste Special, uncheck Formulas, OK, however that is an extra step.

The formula works. You avoid the circular reference by checking "Iterations" at Tools>Options>OpenOffice.org Calc>Calculate.
also it doesn't get recalculated when you open or save the file or make changes any where else on the sheet.

The only time the time changes is when you clear cell A1's contents and retype something in it.

The thing with the OP is he doesn't to want clear cell A1 first, because it contains lots of text and he/they don't want to retype it.They Just want to go in and modify the text and have it update.

I suggested in the other forum a where this question is posted a temp. fix could be to add a "Done" cell. which you could just type one word like "done" and easily delete and retype anytime to update the time stamp.but Of coarse the users would have to remember to update the timestamp.

I think Your are right though a formula alone won't do it all and make it totally user proof.
I tried charlies macros they seem work pretty good and did the job.

aloarjr810 · Post by **aloarjr810** » Sun Jul 18, 2010 3:20 pm

Now probe1 came up with this great custom function for a time stamp:

Code: Select all

Function TimeText

sTime = Format( Now(), "yyyy-mm-dd hh:mm" )
sText = "Updated: "

TimeText = sText & sTime'

End Function

Place this Function code into the My Macros>STANDARD library of your OOo installation to have the function available in any Calc document
Enter =TimeText() into a cell to get something like: Updated: 2010-07-18 15:30

Post by **Charlie Young** » Sun Jul 18, 2010 6:45 pm

Unfortunately, both my macro and the TimeText function suffer from the same malady: the timestamps are updated on a hard recalculation (Ctrl-Shift-F9), and in the case of the TimeText function, this is triggered on File > Open.

Further work is needed.

edit this topic's initial post · Post by **Villeroy** » Sun Jul 18, 2010 6:46 pm

Try macro 3a or 3b in
[Calc,Python,Basic,Base] Several ways to time stamps
A text value is not a calculatable date.

krijes · Post by **krijes** » Sun Jul 18, 2010 6:55 pm

thanx guys,
pls find the attached sheet; it will help you to understand my query more clearly.

I am a newbee to macros and i want to use this sheet as shared sheet on windows NT network hope it will work.

the attached sheet is based on solution give by probe1 in another forum

probe1 wrote:To return something into a Calc cell you need a user defined function, which -in this case- combines a text and date, formatted to your needs.

This is an example
Code: Select all
Function ZeitText

sZeit = Format( Now(), "yyyy-mm-dd hh:mm" ) 
sText = "order from: "

ZeitText = sText & sZeit'

End Function
Place this Function code into the My Macros>STANDARD library of your OOo installation to have the function available in any Calc document

Enter =ZeitText() into a cell to get something like: order from: 2010-07-18 15:30

Now you can change the text (and position) or the format of returned time to your like, couldn't you?

thanx for your time and help

regards
krijes

time_stamp_probe_v01.ods: updated the file as per given formula; but closing and reopen result in current sys timestamp; (12.14 KiB) Downloaded 334 times

krijes · Post by **krijes** » Sun Jul 18, 2010 9:21 pm

Villeroy wrote:Try macro 3a or 3b in
[Calc,Python,Basic,Base] Several ways to time stamps
A text value is not a calculatable date.

ur given solutions in above list is too complex and risky for me to apply and test on a shared sheet which is used by lot of technology challenged users..

ville cant we have a formula for this ; i mean .. i am sure a lot of user are looking for this kind of solution from a spreadsheet of calc caliber; where we dont need to lower the security bar for macros and yet achieve simple and user friendly spreadsheet.

edit this topic's initial post · Post by **Villeroy** » Mon Jul 19, 2010 4:05 pm

You can not do this in any spreadsheet without implementing some kind of macro. The macro simply puts a time stamp into column A when "some other cells" are modified. You've got to install the macro and define "some other cells" through the validation feature.
Use it or lose it.

aloarjr810 · Post by **aloarjr810** » Mon Jul 19, 2010 7:43 pm

While this doesn't change on a cell value change.
Heres another manual timestamp using a validity list. (the users would have to change it.)
But it doesn't change on a save, open, hard recalculate or change on the sheet and you dont have to delete your text.

Select cell where you want the time stamp
go to menu Data > validity
In Criteria>Allow>Cell Range & Show selection list
In "Source" enter NOW() click ok.
Format your cell for your time format.

When somebody updates, they just select the time cell and set the current time from the list.
The time stays til you change it again.

krijes · Post by **krijes** » Wed Jul 21, 2010 12:38 pm

thanks to all for helping me on this, BUT still looking for some REAL solution other then macro... as network security policy does not allow me to use macro on a shared sheet.

Many thanks for all of you.

Best Regards
krijes

edit this topic's initial post · Post by **Villeroy** » Wed Jul 21, 2010 1:53 pm

Ther REAL and macro free solution for this type of problem would be some network capable database with triggers. If you insist on a calculator as database surrogate you have to extend its capabilities one way or the other.
I have no idea how my time-stamp code for Calc could ever violate any kind of network policy. Like all macros it runs in local context, simply putting a number into a certain cell of the local user's spreadsheet triggered by a local user's data entry.

krijes · Post by **krijes** » Wed Jul 21, 2010 2:30 pm

Villeroy wrote: I have no idea how my time-stamp code for Calc could ever violate any kind of network policy. Like all macros it runs in local context, simply putting a number into a certain cell of the local user's spreadsheet triggered by a local user's data entry.

can you pls upload an example calc sheet where entering data on 2nd, 5th and 8th column will trigger time stamp on 3rd,6th and 9th column. i will test it again in my technology challenged shared user environment.(with network security)

Villeroy wrote: some network capable database with triggers.

i have no idea what are these and how to implement it in a technology challenged shared user environment.

regards
krijes

edit this topic's initial post · Post by **Villeroy** » Wed Jul 21, 2010 10:11 pm

krijes wrote: can you pls upload an example calc sheet where entering data on 2nd, 5th and 8th column will trigger time stamp on 3rd,6th and 9th column. i will test it again in my technology challenged shared user environment.(with network security)

regards
krijes

Using my validation triggers this is a matter of seconds: Select columns B,E,H and validate text length=-1 triggering macro vnd.sun.star.script:TimeStamp2Calc.py$NowToColumnAByValidation?language=Python&location=user

For 3 different stamps you need some modified sheet layout or implement some variants with different target columns:

def _NowToColumnCByValidation(sAddr, bOverwrite):
iCol = 2
[...]

def _NowToColumnFByValidation(sAddr, bOverwrite):
iCol = 5
[...]

def _NowToColumnIByValidation(sAddr, bOverwrite):
iCol = 8
[...]

Extract my Python-macro to your user profile and test the attached document with a single target column A.

Post by **Charlie Young** » Thu Jul 22, 2010 6:21 am

I vote for Villeroy's macro over mine. The validity method was something I had not thought of, and would seem to produce fewer problems than attaching listeners to all those cells.

Instead of hardcoding it to column A though, it looks to be simple to modify it to set the timestamp cell just to be one to the right of the column with the validity check:

Code: Select all

def _NowToColumnAByValidation(sAddr, bOverwrite):
    doc = XSCRIPTCONTEXT.getDocument()
    s,r = splitStringAddress(sAddr)
    sh = doc.Sheets.getByName(s)
    rg = sh.getCellRangeByName(r)
    iRow = rg.RangeAddress.StartRow
    ''' Set Timestamp column to be one to the right '''
    iCol = rg.RangeAddress.StartColumn + 1
    c = sh.getCellByPosition(iCol, iRow)
    if bOverwrite or not c.getFormula():
        printStamp(c)

Seems to work, though one might also want to change the macro's name from NowToColumnAByValidation to be perfectly clear.

edit this topic's initial post · Post by **Villeroy** » Thu Jul 22, 2010 8:11 am

Charly,
Thank you for the fix. I'd suggest _NowToNextColumnByValidation as name of your routine.

Turning constant iCol into a parameter brings some flexibility:

Code: Select all

def _NowToColumn_ByValidation(sAddr, bOverwrite, iCol):
    doc = XSCRIPTCONTEXT.getDocument()
    s,r = splitStringAddress(sAddr)
    sh = doc.Sheets.getByName(s)
    rg = sh.getCellRangeByName(r)
    iRow = rg.RangeAddress.StartRow
    c = sh.getCellByPosition(iCol, iRow)
    if bOverwrite or not c.getFormula():
        printStamp(c)

def NowToEmptyColumnAByValidation(sFormula, sAddr): 
    '''Current time to empty column A, triggered by failing validation'''
# passing over column #0 (A)
    _NowToColumn_ByValidation(sAddr, False, 0)

def NowToColumnAByValidation(sFormula, sAddr):
    '''Current time to column A, triggered by failing validation'''
# passing over column #0 (A)
    _NowToColumn_ByValidation(sAddr, True, 0)

# for any other column you need...
def NowToEmptyColumnCByValidation(sFormula, sAddr): 
# passing over column #2 (C)
    _NowToColumn_ByValidation(sAddr, False, 2)

def NowToColumnCByValidation(sFormula, sAddr):
# passing over column #2 (C)
    _NowToColumn_ByValidation(sAddr, True, 2)

krijes · Post by **krijes** » Sat Jul 31, 2010 7:14 pm

many thanx ville, charlie. but i cant use in my server security environment.. server and security is outsourced to third party here and we cant tweak them ..

edit this topic's initial post · Post by **Villeroy** » Sat Jul 31, 2010 7:18 pm

krijes wrote:many thanx ville, charlie. but i cant use in my server security environment.. server and security is outsourced to third party here and we cant tweak them ..

How can the evil power know that a local office-macro typed data into spreadsheet cells?

krijes · Post by **krijes** » Sat Jul 31, 2010 7:57 pm

will post the screen shot from shared user machine..., once i get there

gordix1 · Post by **gordix1** » Thu Jun 07, 2012 4:29 pm

Thanks a lot for all the posts above.
On using the macro TimeStamp2Calc I am facing a problem, it works really well unless you paste the value into the cell.

Any ideas??

Thanks

edit this topic's initial post · Post by **Villeroy** » Thu Jun 07, 2012 6:25 pm

gordix1 wrote:Thanks a lot for all the posts above.
On using the macro TimeStamp2Calc I am facing a problem, it works really well unless you paste the value into the cell.

Any ideas??

Thanks

A database is the one and only clean solution for this. When you paste cells over other cells, the other cells are completely overwritten (values, formulas, formatting, conditional formatting, validation included).
The dirty hack would be a listener macro as suggested by Charly. Without being really sure, I think that this mechanism wouldn't be affected by a copy and paste.
Spreadsheets are horrible tools for advanced list keeping even though the vast majority of today's users use it exactly for that purpose.

Post by **Charlie Young** » Thu Jun 07, 2012 6:53 pm

Villeroy wrote:
gordix1 wrote:Thanks a lot for all the posts above.
On using the macro TimeStamp2Calc I am facing a problem, it works really well unless you paste the value into the cell.

Any ideas??

Thanks
A database is the one and only clean solution for this. When you paste cells over other cells, the other cells are completely overwritten (values, formulas, formatting, conditional formatting, validation included).
The dirty hack would be a listener macro as suggested by Charly. Without being really sure, I think that this mechanism wouldn't be affected by a copy and paste.
Spreadsheets are horrible tools for advanced list keeping even though the vast majority of today's users use it exactly for that purpose.

I've been looking at this, and I suspect it is a bug. Even if you do a Paste Special, so as not to change the validation settings, it still doesn't work.

I also thought about writing some macro to attach to the documents "'Modified' status was changed" event, having the macro check if the current selection is a cell with the validation condition, and running the timestamp macro if it does. I can't think of a way to make this foolproof though; if the modified status were to be changed by something other than a cell entry, say by saving the document, it might also change a timestamp inappropriately.

Maybe something can be done with the listeners, but I think that would require attaching a listener to every cell in question, and would be very cumbersome.

edit this topic's initial post · Post by **Villeroy** » Thu Jun 07, 2012 7:00 pm

LibreOffice provides events in the context menu of a sheet tab.

Right-click>Sheet Events...
Activate Doc
DeActivate Doc
Selection Changed
Double Click
Right Click
Forumulas calculated
Content changed

Judging from my experience with LibO (and adding a grain of fatalism) it is one of those good looking but untested features which may work, crash or do nothing at all.

edit this topic's initial post · Post by **Villeroy** » Thu Jun 07, 2012 11:59 pm

I never noticed the sheet events in OpenOffice but the feature exists in AOO 3.4 as well.
This code puts the time stamp into column A in whatever row you modify (edit, delete, paste) some cell or rectangular cell range.

Code: Select all

import uno
from com.sun.star.sheet.FillDirection import TO_BOTTOM, TO_TOP, TO_RIGHT, TO_LEFT
from com.sun.star.sheet.FillMode import SIMPLE

def NowToColumnA(r):
    '''To be called by LibreOffice sheet event "Content Changed". 
    Argument r is the modified cell or range'''
    a = r.getRangeAddress()
    sh = r.getSpreadsheet()
    oTarget = sh.getCellRangeByPosition(0, a.StartRow, 0, a.EndRow)
    c = oTarget.getCellByPosition(0,0)
    c.setFormula("=NOW()")
    oTarget.fillSeries(TO_BOTTOM, SIMPLE,0,0,0)
    oTarget.setDataArray(oTarget.getDataArray())

g_exportedScripts = NowToColumnA,

Post by **karolus** » Fri Jun 08, 2012 1:53 am

Hallo

Slightly changed:

Code: Select all

from datetime import datetime as d

def NowToColumnA(r):
    '''To be called by LibreOffice sheet event "Content Changed".
    Argument r is the modified cell or range'''
    a = r.getRangeAddress()
    sh = r.getSpreadsheet()
    oTarget = sh.getCellRangeByPosition(0, a.StartRow, 0, a.EndRow)
    f = '%Y-%m-%d %H:%M:%S'
    nows = ((d.now().strftime( f ),),) * (1 + a.EndRow - a.StartRow)
    oTarget.setFormulaArray( nows )

Karo

Time/Stamp on value change

Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change

Re: Time/Stamp on value change