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.
Table of Contents
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:
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:
