Date and Time Processing in LibreOffice Calc using Macro

2 min


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:

Date and Time Processing Example Output
Date and Time Processing Example Output

Looking for Something Else?

If you are looking for something else in LibreOffice macro tutorials Or wants to learn more about it, please follow the below link for the complete Macro Tutorials Index:


Arindam

Creator of debugpoint.com. All time Linux user and open-source supporter. Connect with me via Telegram, Twitter, LinkedIn, or send us an email.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments