Ask Your Question
1

A simple Timer with dates & times

asked 2017-05-21 14:59:09 +0200

creativeflower gravatar image

I would like to share my solution to a simple timer that logs the End date - Startdate = Session time into one row. It also calculates the totals.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2017-05-21 15:02:14 +0200

creativeflower gravatar image

updated 2018-08-17 19:56:46 +0200

here is a link to the improved sheet: https://www.file-upload.net/en/downlo... It auto plots the spent time, the included macro is below

picture

REM  *****  BASIC  *****

Sub MyDateFormat(Cell As Object, NumberFormats As Object, NumberFormatString As String)  

Dim NumberFormatId As Long
Dim LocalSettings As New com.sun.star.lang.Locale  

LocalSettings.Language = "en"
LocalSettings.Country = "us"

NumberFormatId = NumberFormats.queryKey(NumberFormatString, LocalSettings, True)
If NumberFormatId = -1 Then
   NumberFormatId = NumberFormats.addNew(NumberFormatString, LocalSettings)
End If

Cell.NumberFormat = NumberFormatId
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub StartTimer
Dim Doc As Object
Dim Sheet As Object
Dim CurrRow As Integer

Doc = ThisComponent
Sheet = Doc.Sheets.getByName("timer") ' Doc.Sheets(0) 

CellCount = Sheet.getCellByPosition(1, 0)
CellCount.Value = CellCount.Value + 1
CurrRow = CellCount.Value

CellStart = Sheet.getCellByPosition(0, CurrRow) ' ->, v
CellStart.Value = NOW()  

'set format
Dim MyFormat As String
MyFormat = "MM/DD/YY HH:MM AM/PM"
MyDateFormat(CellStart, Doc.NumberFormats, MyFormat)
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub EndTimer
Dim Doc As Object
Dim Sheet As Object
Dim CurrRow As Integer

Doc = ThisComponent
Sheet = Doc.Sheets.getByName("timer") ' Doc.Sheets(0) 

CellCount = Sheet.getCellByPosition(1, 0) 
CurrRow = CellCount.Value

CellStart = Sheet.getCellByPosition(0, CurrRow)
CellEnd = Sheet.getCellByPosition(1, CurrRow)
CellEnd.Value = NOW()

'insert diff formula
CellSession = Sheet.getCellByPosition(2, CurrRow) 
CellSession.Formula = "= B" + CStr(CellEnd.CellAddress.Row() + 1) + "- A" + CStr(CellStart.CellAddress.Row() + 1)

'set formats
Dim MyFormat As String
MyFormat = "MM/DD/YY HH:MM AM/PM"
MyDateFormat(CellEnd, Doc.NumberFormats, MyFormat)
MyFormat = "HH:MM"
MyDateFormat(CellSession, Doc.NumberFormats, MyFormat)
End Sub
edit flag offensive delete link more

Comments

Thank you for this handy code snippet @creativeflower Btw, i think that cell C3 seen in your preview image has a wrong NumberFormat set, to display the duration

librebel gravatar imagelibrebel ( 2017-05-22 10:22:57 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-05-21 14:59:09 +0200

Seen: 300 times

Last updated: Aug 17 '18