Output last modified date and time in a cell [LibreOffice Calc]

English
,
#1

Hello,

I want to output the document’s last modified date in a cell in Calc.
Whenever I make any changes inside any cell of the document, this cell should get called and automatically update to the current time.

Also, is this possible for pages only?

Output the current date and time inside a cell in Page “Test” if any change is being made inside any cell of page “Test”.

Thank you!

I got it to work using this function:

Function CalcDoc_Modify_Date()
Dim sTemp
' work with DocInfo
With ThisComponent.DocumentProperties.ModificationDate
   ' set return format
   ' force month and day to two digits
   sTemp = _
   Format( .Month, "0#" ) & "/" & _
   Format( .Day, "0#" ) & "/" & _
   .Year & " " & _
   Format( .Hours, "0#" ) & ":" & _
   Format( .Minutes, "0#" ) 
   End With
' assign func return value
CalcDoc_Modify_Date = sTemp
End Function

In your spreadsheet, enter =CALCDOC_MODIFY_DATE() in a cell to call this function.
(Getting date of last change as a cell value in Open Office Calc - Super User)

…but then it always stayed on the date forever and didn’t “update live” when I made a change, which I want it to do.

#2

Just =NOW() :slightly_smiling_face:

#3

I’m aware of “NOW”, but how can I make it update whenever I make a change?

#4

Please, try.
NOW:

Returns the computer system date and time. The value is updated when you recalculate the document or each time a cell value is modified.
This function is always recalculated whenever a recalculation occurs.

#5

Omg, I didn’t know it was auto updating

Thank you so much

1 Like
#6

You know that NOW() is a “volatile” function?
It will be recalculated on next to any event even if you “clear” an empty cell.
It will NOT recalculate if >Data>Calculate>AutoCalculate is disabled.

#7

Thank you for that info.
Turns out that “NOW” isn’t what I want - because it gets triggered even when I just open the document; making it obsolete for my specific use case.
And if I disable AutoCalculate, it’s also not gonna work.

I want a cell which displays “Last modified” and “NOW” gets triggered even when “Last opened”.
I want to open the document and see inside the cell “Ah, I made my last change 2 weeks ago”, and this use case isn’t achievable with “NOW” because it gets updated even when only reading.

#8

Event driven approach. See Tools>Customize… Events
savedAt.ods (17.0 KB)

#9

Thank you!
Can this be done on sheets individually as well? Because these events are globally.
How could this be achieved?

"Output the current date and time inside cell A1 in sheet “Test” if any change is being made inside any cell of sheet “Test”.

#10

It is now high time to establish clearly what is really desired.

  • Last Modified may be seen as a property of the file as @Villeroy assumed. In this case the DateTime can be retrieved using the DocumentProperties service:
    dtValue = ThisComponent.DocumentProperties.ModificationDate
    This is misleading in two ways:
    : 1) You get a propertty of the file, not one of the document under ongoing editing.
    : 2) The value isn’t a date, but a date-time structure.
  • Last Modified is meant to mean the dt of the last actual change of any direct content of the living document which is not yet saved to a file based on the current state.
    This seemed to be what the questioner wanted. It’s quite complicated to achieve, however, because spreadsheetzs aren’t history aware.
  • Something else??? Tell exactly.
#11

What is desired:

I have a spreadsheet which has multiple sheets. On the first page “OVERVIEW”, which contains links (“Targets”) to the other pages, I have a cell at the bottom which is called “Document last modified on:” and I always inserted the date manually. The intention was that I don’t use the document every day and wanted to see “Ah, I added something 8 days ago”. I’m fully aware that I can see this information in the file’s properties of probably every operating system, but I just thought it was handy.

And, well, the whole point now was that I didn’t want to always update the date in the cell manually every time I change something.

Furthermore, it would be handy to insert this specific “timestamp-cell” in every page individually: That way I could see, when just viewing the file “ah, I changed something on sheet #3 two weeks ago”. - This specific information wouldn’t always correlate with the file’s modification date.

#12

To have a modification date for a single sheet, just use the “modified contents of each single sheet” event. In practice, with each modification you will update the date and time in each specific cell of each sheet, a macro that works continuously to every modification and not every save.

#13

Do as @Villeroy demonstrated then.

But you don’t need to write the modification date-time explicitly on “save events” to a cell. It is stored anyway, and you can retrieve it when the document is opened next time as already described.

To write such information to every sheet somewhere would restrict cell usage too much.
To have it always visible I personally prefer to show much extended information in the title bar. This looks like

/C:/Users/MyUserID/Downloads/savedAt.ods?md=20230923_184018&gv=LibreOffice/7.5.3.2&cv=LibreOffice V7.5.3.2&tN=MyDefault# - Calc

then and can easily also be written to a cell. The left parts of the info equations and their meaning are

  • md= modification date-time (last saving) formatted to YYYYMMDD_HHMMSS
  • gv= version of LibO or AOO used when the document was last saved
  • cv= currently Running version of LibreOffice
  • tN= template name
    The extended info is shaped as a query part.

Of course this can be reduced/enriched/modified in many ways.
The needed code and the preparations to make it work aren’t quite simple, however.

(If I find the time I will probably publish my code and a guide one day. - Very doubtable, however.)

#14

Did you actually consider this in detail? I’m afraid it’s much more complicated than you think. Just consider the trouble you need to take to avoid to write a “modified date-time” to the respective cell if the user inadvertently had pressed the Del key for an empty cell.

#15

If you read me carefully I wrote that you would have a macro that works continuously!!!
For every change, even if unwanted, but if that’s what the user wants, record every process on the file… and keep track of this on every single sheet… I simply suggested a “solution”.
sorry my english from google translate

#16

See example.
The date the sheet named shName was last modified is written to a cell named mod_shName.

Option Explicit
Global oModifyListener As Object

Sub SetModifyListener()
    Dim oSheet 
    oModifyListener = createUnoListener("SheetModify_","com.sun.star.util.XModifyListener")
    For Each oSheet In ThisComponent.Sheets  
      oSheet.addModifyListener oModifyListener
    Next oSheet   
End Sub

Sub SheetModify_modified(oEvent)
    Dim s As String
    s="mod_" & oEvent.Source.Name
    With ThisComponent.NamedRanges
      If .HasByName(s) Then 
        .getByName(s).referredCells.setValue Cdbl(Now())
      End If  
    End With  
End Sub

Sub SheetModify_disposing(oEvent)
End Sub

Sheet Modified.ods (14.3 KB)

#17

Thank you, that’s exactly what I wanted!

I’m having trouble implementing it into my actual document. I played around for like 40 minutes but I don’t quite get it because I’ve never done something like this in Calc. Do I need to change something in the code or is it a issue in my document (I guess so because it’s variables)?

See screenshots:

  • I enabled Macros
  • I copy pasted the code inside “mod_date” in my spreadsheet “test.ods”

image
image1506×524 64.8 KB

My sheet 1 is called “OVERVIEW” and my sheet 2 is called “Test_Page”.
When I now type “=mod_test_page” in a cell in sheet 1, I get “#NAME?”. What is my issue?

I’m pretty sure this is just a tiny mistake because it worked as expected in the file you sent me, but I’m not sure how to “abstract” the logic to other spreadsheets.
This is probably my last question left because it’s exactly what I wanted!

Thanks

#18

The same proposal I made with a listener.
I attach an example with a small macro associated with the content modified event of sheets Sheet1 and Sheet2

Sub Modify
Doc = ThisComponent
Sh = Doc.Sheets.getByname("OVERVIEW")
Sheet = Doc.CurrentController.ActiveSheet
If Sheet.name = "Sheet1" Then 
   Sh.getCellRangeByname("B2").vALUE = Now()
Elseif Sheet.name = "Sheet2" Then 
   Sh.getCellRangeByname("B3").vALUE = Now()
End If
End Sub

Modify.ods (11.5 KB)

#19

Let’s try to look at the example (Sheet Modified.ods).

  1. When the file is opened, the Standard.Module1.SetModifyListener macro is executed (Menu / Tools / Customize, tab Events).
  2. With each change in each sheet, control is transferred to the macro SheetModify_modified. This macro specifies the name of the sheet on which the changes occurred (oEvent.Source.Name). Next, among the document names (NamedRanges), the corresponding name with the prefix “mod_” is searched. If there is such a name, then the value of the current change time is entered in the corresponding cell.
  3. In the example document there are names mod_Sheet1, mod_Sheet2, mod_Sheet3, referring to cells B2, B3, B4 of the OVERVIEW sheet. Names are managed through the “Manage Names” dialog (Menu / Sheet / Named Ranges and Expressions... / Manage...).

Try to improve your example. If there are problems, upload your file to the forum.
Good luck!

#20

I got it to work. Thank you very much!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.