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:
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
We bring the latest tech, software news and stuff that matters. Stay in touch via Telegram, Twitter, YouTube, and Facebook and never miss an update!
