Several ways to time stamps
This script was published by Villeroy at the Code Snippets Forum.
Type =NOW() without entering the formula.
Hit {{#switch:f9
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = F9
}} {{#if:| + {{#switch:{{{2}}}
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = {{{2}}}
}} }}{{#if:| + {{#switch:{{{3}}}
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = {{{3}}}
}} }}{{#if:| + {{#switch:{{{4}}}
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = {{{4}}}
}} }}{{#if:| + {{#switch:{{{5}}}
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = {{{5}}}
}} }}{{#if:| + {{#switch:{{{6}}}
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = {{{6}}}
}} }} to calculate the formula right on the input line.
Then hit {{#switch:enter
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = Enter
}} {{#if:| + {{#switch:{{{2}}}
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = {{{2}}}
}} }}{{#if:| + {{#switch:{{{3}}}
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = {{{3}}}
}} }}{{#if:| + {{#switch:{{{4}}}
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = {{{4}}}
}} }}{{#if:| + {{#switch:{{{5}}}
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = {{{5}}}
}} }}{{#if:| + {{#switch:{{{6}}}
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = {{{6}}}
}} }} twice (confirm and enter).
Using a shortcut to NOW() (a tiny little bit shorter):
Call {{#if: Insert |Insert}}{{#if: Names | → Names}}{{#if: Define… | → Define…}}{{#if: | → {{{4}}}}}{{#if: | → {{{5}}}}}{{#if: | → {{{6}}}}}{{#if: | → {{{7}}}}}{{#if: | → {{{8}}}}}{{#if: | → {{{9}}}}}{{#if: | → {{{10}}}}} ( {{#switch:ctrl
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = Ctrl
}} {{#if:F3| + {{#switch:f3
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = F3
}} }}{{#if:| + {{#switch:{{{3}}}
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = {{{3}}}
}} }}{{#if:| + {{#switch:{{{4}}}
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = {{{4}}}
}} }}{{#if:| + {{#switch:{{{5}}}
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = {{{5}}}
}} }}{{#if:| + {{#switch:{{{6}}}
| shift = ⇧ Shift
| up = ↑
| down = ↓
| > = →
| < = ←
| enter = ↵ Enter
| cmd = ⌘ Cmd
| command = ⌘ Cmd
| opt = ⌥ Opt
| option = ⌥ Opt
| tab = Tab ⇆
| backspace = ← Backspace
| << = ← Backspace
| bs = ← Backspace
| * = *
| # = #
| ; = ;
| : = :
| #default = {{{6}}}
}} }})
Name: n
Refers to: NOW()
Hit Add , then OK
Type into a cell: =n F9,Enter,Enter
NOW() returns the full date and time info as a formatted number. Having such a full stamp in a A1, =INT(A1) returns the day-portion, MOD(A1;1) returns the time-fraction only. You can use number formats to display only the date or the time without changing the value.
Original code
import uno
def getActiveCell(oView):
'''Desparately missing in API. We extract from view data.'''
sData = oView.getViewData()
oSheet = oView.getActiveSheet()
as1 = sData.split(";")
lSheet = long(as1[1])
sDum = as1[lSheet +3]
delim = '/' in sDum and '/' or '+'
as2 = sDum.split(delim)
lCol = (as2[0])
lRow = (as2[1])
return oSheet.getCellByPosition(lCol,lRow)
def printStamp(oCell):
'''Put the current time into the active cell.
No formatting intended. Apply any date/time formatting you like.'''
oCell.setFormula("=NOW()")
oCell.setValue(oCell.getValue())
def _NowToColumnAByValidation(bOverwrite):
iCol = 0
doc = XSCRIPTCONTEXT.getDocument()
c = getActiveCell(doc.getCurrentController())
sh = c.getSpreadsheet()
iRow = c.CellAddress.Row
c = sh.getCellByPosition(iCol, iRow)
if bOverwrite or not c.getFormula():
printStamp(c)
def _NowToTimeStampCell(bOverwrite):
thisComponent = XSCRIPTCONTEXT.getDocument()
oNames = thisComponent.NamedRanges
oName = oNames.getByName("TimeStamp")
oRg = oName.getReferredCells()
oCell = oRg.getCellByPosition(0,0)
if bOverwrite or not oCell.getFormula():
printStamp(oCell)
def NowToEmptyColumnAByValidation(*a):
'''Current time to empty column A, triggered by failing validation'''
_NowToColumnAByValidation(False)
def NowToColumnAByValidation(*a):
'''Current time to column A, triggered by failing validation'''
_NowToColumnAByValidation(True)
def NowToTimeStampCell(*a):
'''Put current time into a cell named "TimeStamp"'''
_NowToTimeStampCell(True)
def NowToEmptyTimeStampCell(*a):
'''Put current time into an empty cell named "TimeStamp"'''
_NowToTimeStampCell(False)
def NowToActiveCell(*a):
'''Put current time into the currently active input cell'''
oDoc = XSCRIPTCONTEXT.getDocument()
oView = oDoc.getCurrentController()
oCell = getActiveCell(oView)
printStamp(oCell)
def NowToRightNeigbour(*a):
'''Put current time into the currently active input cell'''
oDoc = XSCRIPTCONTEXT.getDocument()
oView = oDoc.getCurrentController()
oA = getActiveCell(oView)
a = oA.getCellAddress()
sh = oDoc.Sheets.getByIndex(a.Sheet)
oCell = sh.getCellByPosition(a.Column +1, a.Row)
printStamp(oCell)
g_exportedScripts = NowToActiveCell,NowToTimeStampCell,NowToEmptyTimeStampCell,NowToColumnAByValidation,NowToEmptyColumnAByValidation,NowToRightNeigbour,
