Date and Time Processing in LibreOffice Calc using Macro

This tutorial explains the basic date and time processing in basic macro in LibreOffice Calc.

In basic, a Date is a datatype that stores date and time values. By default, it holds Jan 1 year 0001 midnight. We will pick any date and process it in different ways. Let’s explain the concept using some examples.

Note: This tutorial assumes you know how to create and run a macro. If you are new, you can check out this tutorial first and come back to this page.

Processing date and time via LibreOffice macro (Basic)

1. Add days and months to a date

Declaring a date variable can be done using below code:

Dim my_date As Date

Once declared, we will use DateValue function. This function helps to convert the text-formatted date to a date format. We will put this date in a LibreOffice Calc cell.

my_date = DateValue("Nov 20, 2014")
my_cell = ThisComponent.Sheets(0).getCellbyPosition(1,1)
my_cell.String = my_date

To add three days to the above date, use the DateAdd() function. This function takes the first argument as to which part of the date needs to be increased. If you pass as “d” as in the day, the date would be increased by the day. If you use “m”, it will increase the date as months.

For example, use the following code snippet to increase the date by three days.

my_cell = ThisComponent.Sheets(0).getCellbyPosition(1,3)
my_cell.String = DateAdd("d", 3, my_date)

Similarly, to increase the date by three months, use the below example:

my_cell = ThisComponent.Sheets(0).getCellbyPosition(1,5)
my_cell.String = DateAdd("m", 3, my_date)

Note: If you need to understand the concept of ThisComponent and getCellbyPosition, refer to this guide.

2. Formatting a Date

Formatting a date is an important piece of task for several use cases. This example shows how to extract the day, month and year from a date which is present in a LibreOffice Calc cell. Here are various ways of formatting a date. Each can be used with a valid date as an argument.

Output: 20 – 11 – 14

my_cell = ThisComponent.Sheets(0).getCellbyPosition(1,7)
my_cell.String = Format(my_date, "d - m - yy") ' 20 - 11 - 14

Format – Output: 20 Nov 2014

my_cell = ThisComponent.Sheets(0).getCellbyPosition(1,9)
my_cell.String = Format(my_date, "d MMM yyyy") '20 Nov 2014

Format – Output: 20 November 2014

my_cell = ThisComponent.Sheets(0).getCellbyPosition(1,11)
my_cell.String = Format(my_date, "d MMMM yyyy") '20 November 2014

Format – Output: Thursday, 20 Nov 2014

my_cell = ThisComponent.Sheets(0).getCellbyPosition(1,13)
my_cell.String = Format(my_date, "dddd, d MMM yyyy") 'Thursday, 20 Nov 2014

Format – Output: Thursday, 20 November 2014

my_cell = ThisComponent.Sheets(0).getCellbyPosition(1,15)
my_cell.String = Format(my_date, "dddd, d MMMM yyyy") 'Thursday, 20 November 2014

3. Time Processing

Like date, system time and time component of a date can also be processed using a macro. Below are some time functions and their features with sample code.

Now: Shows current date and system time, including hour, minute and seconds

my_cell = ThisComponent.Sheets(0).getCellbyPosition(1,17)
my_cell.String = Now

Hour: Shows Hour

my_cell = ThisComponent.Sheets(0).getCellbyPosition(1,19)
my_cell.String = Hour(Now)

Minute: Shows Minute

my_cell = ThisComponent.Sheets(0).getCellbyPosition(1,21)
my_cell.String = Minute(Now)

Second: Shows Second

my_cell = ThisComponent.Sheets(0).getCellbyPosition(1,23)
my_cell.String = Second(Now)

Output

Putting all the above date and time processing codes in a Macro, here is the output in Calc: