Date and Time Processing in LibreOffice Calc using Macro

Advertisement

In this tutorial I will show how to do basic date processing in basic via LibreOffice CALC cells. In basic, Date is a datatype used to store date and time values. By default, it holds Jan 1 year 0001 midnight. We will pick any date and process it in different ways.

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 3 days to the above date, use below [cci_vb]DateAdd()[/cci_vb] function. This function takes first argument as which part of the date needs to be increased. If you pass as “d” as in day, the date would be increased on day. If you use “m”, it would increase the date as months.

Increase the date by 3 days:

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

Increase the date by 3 months:

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

Formatting a Date

Formatting a date always necesary for various calculations, macro processings especially when you need to extract the day, month, year, Weekday etc.
Here are various ways of formatting a date. Each can be used with a valid date as argument.

Format – 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

Time Processing

Like date, system time also can be captured using macro. Below are some time functions and their features.

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 below link for complete Macro Tutorials Index:

LibreOffice Macro Tutorial Index

You may also like...

Translate »