[Solved] Need Datestamp Macro
[Solved] Need Datestamp Macro
Needs to be in this format: YY.MM.DD
Will look like this: 17.09.27
Thanks in advance!
Will look like this: 17.09.27
Thanks in advance!
Last edited by daveg7 on Fri Sep 29, 2017 2:14 am, edited 2 times in total.
OpenOffice 4.1.3 with Windows 10
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
Re: Need Datestamp Macro
Try to search THIS FORMUM for the search word "timestamp". See search.php?keywords=timestamp&fid%5B0%5D=9 .
If you have remaining questions come back after reading, please.
Also:
Never use 2-digit-years! Don't you remeber the "y2k" hysteria and the related information around everywhere less than 20 years ago?
Make sure you cannot do things in a simpler way before asking for "macros", There are many disadvantages. Only Excel enthusiasts prefer macros for everything.
What about assigning a keyboard-shortcut to the command 'Insert Current Date'. The cell content created will then be numeric as is standard in spreadsheets. To get displayed the recommended format YYYY-MM-DD simply prepare a 'Date' cell style with it and assign it to the cells (columns) expected to have to take a date-stamp.
If you have remaining questions come back after reading, please.
Also:
Never use 2-digit-years! Don't you remeber the "y2k" hysteria and the related information around everywhere less than 20 years ago?
Make sure you cannot do things in a simpler way before asking for "macros", There are many disadvantages. Only Excel enthusiasts prefer macros for everything.
What about assigning a keyboard-shortcut to the command 'Insert Current Date'. The cell content created will then be numeric as is standard in spreadsheets. To get displayed the recommended format YYYY-MM-DD simply prepare a 'Date' cell style with it and assign it to the cells (columns) expected to have to take a date-stamp.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Need Datestamp Macro
I had done your search before posting, but nothing seemed to work for me.
Thanks for your input, but I've worked for decades with spreadsheets since VisiCalc. Though I know that sometimes an old horse doesn't know where he's going, I think I know what I need for my client--and yes, it's a macro.
Danken!
Thanks for your input, but I've worked for decades with spreadsheets since VisiCalc. Though I know that sometimes an old horse doesn't know where he's going, I think I know what I need for my client--and yes, it's a macro.
Danken!
OpenOffice 4.1.3 with Windows 10
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
OpenOffice 4.1.3 with MacOS 10.4
Excel 2003 and 2010 with Windows 10 Pro and 32 GB Ram
Excel with MacOS 10.4
Re: Need Datestamp Macro
Code: Select all
REM ***** BASIC *****
REM First variant
Sub insertCDnumFormatted()
Dim dFrame As Object
Dim dH As Object
dFrame = ThisComponent.CurrentController.Frame
dH = CreateUnoService("com.sun.star.frame.DispatchHelper")
Dim args1(0) As New com.sun.star.beans.PropertyValue
args1(0).Name = "StringName"
args1(0).Value = Format(Int(Now()), "YYYY-MM-DD")
REM Expect problems with different locales.
dH.ExecuteDispatch(dFrame, ".uno:EnterString", "", 0, args1())
'dH.ExecuteDispatch(dFrame, ".uno:JumpToNextCell", "", 0, Array())
End Sub
REM Second variant
Sub inserCDisoText()
theFC = focusCell()
theFC.String = Format(Int(Now()), "YYYY-MM-DD")
REM Expect problems with different locales.
End Sub
REM Helper needed for the second version:
Function focusCell(Optional pDoc) As Object
REM Concept by Andreas Säger
REM Responsible for this variant: Wolfgang Jäger
If IsMissing(pDoc) Then pDoc = ThisComponent
If NOT pDoc.SupportsService("com.sun.star.sheet.SpreadsheetDocument") Then
Exit Function
End If
vD = pDoc.CurrentController.ViewData
vDSplit = Split(vD, ";")
sheetNum = 0 + vDSplit(1)
theSheet = pDoc.Sheets(sheetNum)
vDtheSheet = Split(vDSplit(sheetNum + 3), "/")
fC = theSheet.GetCellByPosition(vDtheSheet(0), vDtheSheet(1))
focusCell = fC
End Function
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Need Datestamp Macro
FormulaLocal is easier and avoids locale problems:
works fine with full ISO format string "YYYY-MM-DD HH:MM:SS.00"
If the cell is not formatted explicitly (number format "General"), the entry of an ISO date/time triggers the default date/time format.
Code: Select all
oCell = focusCell()
oCell.FormulaLocal = Format(Now(), "YYYY-MM-DD")
If the cell is not formatted explicitly (number format "General"), the entry of an ISO date/time triggers the default date/time format.
Edit: fixed typo and property assignment. |
Last edited by Villeroy on Thu Sep 28, 2017 2:32 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Need Datestamp Macro
(Maybe I overread the hint concerning the expectable locale problems the first time. Now I got it. But how will this be achieved.)
Hmm. You surely found my "Second variant" which assigns the Formatted result to the .String property of the cell under focus.
In what way should it be preferrable to assign the same result to the .FormulaLocal property?
BTW: In the way you posted it (the typo rectified), I think no assignment is done. You would either need to use a Set method (which does not exist for .FormulaLocal) or an explicit assignment like
OK. In LibO V 5.4.1. this leads to a numeric content formatted correctly. But where is this behaviour specified and who did promise that this will not be changed without notice?
Yes, my preferred solution creates text content, but this in a format explicitly specified to be recognised numeric.
The "First variant" mimics a manual entering and has the same effect as yours, which seems more expectable to me in this case..
Hmm. You surely found my "Second variant" which assigns the Formatted result to the .String property of the cell under focus.
In what way should it be preferrable to assign the same result to the .FormulaLocal property?
BTW: In the way you posted it (the typo rectified), I think no assignment is done. You would either need to use a Set method (which does not exist for .FormulaLocal) or an explicit assignment like
Code: Select all
oCell.FormulaLocal = Format(Now(), "YYYY-MM-DD")
OK. In LibO V 5.4.1. this leads to a numeric content formatted correctly. But where is this behaviour specified and who did promise that this will not be changed without notice?
Yes, my preferred solution creates text content, but this in a format explicitly specified to be recognised numeric.
The "First variant" mimics a manual entering and has the same effect as yours, which seems more expectable to me in this case..
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Need Datestamp Macro
It is not documented. ISO date input is recognized since version one. I tested the correct behaviour with all built-in locale settings.OK. In LibO V 5.4.1. this leads to a numeric content formatted correctly. But where is this behaviour specified and who did promise that this will not be changed without notice?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Need Datestamp Macro
ISO8601 extemded (YYYY-MM-DD) is, however specified to be accepted for the conversion of text to number by the VALUE() function under 6.13.34 of 'OpenFormula'. Let us hope that everybody takes it as a concludent necessity that automatic conversion after entering 'keystroKes' into cells should also accept that ISO conforming format in the same way.
However, I did not understand for what reason an assignment to the cell property .FormulaLocal should act as if the string was entered into the cell.
However, I did not understand for what reason an assignment to the cell property .FormulaLocal should act as if the string was entered into the cell.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Need Datestamp Macro
FormulaLocal is the string you see in the formula bar. When you set the FormulaLocal you enter something into the formula bar which may be evaluated as a number or not. When you enter a special number into the formula bar, you get the matching default format for the entered subtype.Lupp wrote:However, I did not understand for what reason an assignment to the cell property .FormulaLocal should act as if the string was entered into the cell.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Need Datestamp Macro
Thanks.
Lacking access to actual documentation (or being too dull / too lazy / too uninspired) I am stumped again and again. Why 'Formula'? Ok. The 'Formula bar' suffers from the confusion too, but the term didn't look to me as if relevant for naming any properties. At least for me the so called formula bar is a little editor for any cell content.
Lacking access to actual documentation (or being too dull / too lazy / too uninspired) I am stumped again and again. Why 'Formula'? Ok. The 'Formula bar' suffers from the confusion too, but the term didn't look to me as if relevant for naming any properties. At least for me the so called formula bar is a little editor for any cell content.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Need Datestamp Macro
There is always a formula, a value and a string even when the cell is blank.
Blank: formula="", string="", value=0
Text: formula=text, string=text, value=0
Numeric text: formula='0123, string=0123, value=0
Number: formula=1.234, string=numeric string according to number format, value=1.234
Calculation: formula=expression starting with =, string=numeric string or text, value=decimal number or 0 in case of text.
In case of number or calculation the FormulaLocal may differ from the unlocalized English formula. FormulaLocal may have a comma as decimal separator, the function names may differ and special numbers are formatted strings. The unlocalized get/setFormula() is always an unformatted decimal string with point as decimal separator.
Blank: formula="", string="", value=0
Text: formula=text, string=text, value=0
Numeric text: formula='0123, string=0123, value=0
Number: formula=1.234, string=numeric string according to number format, value=1.234
Calculation: formula=expression starting with =, string=numeric string or text, value=decimal number or 0 in case of text.
In case of number or calculation the FormulaLocal may differ from the unlocalized English formula. FormulaLocal may have a comma as decimal separator, the function names may differ and special numbers are formatted strings. The unlocalized get/setFormula() is always an unformatted decimal string with point as decimal separator.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice