We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
We're aware that some users are experiencing technical issues which the team are working to resolve. See the Community Noticeboard for more info. Thank you for your patience.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!
Small question about LibreOffice Calc
Options

JohnB47
Posts: 2,668 Forumite


Hi. I use LibreOffice Calc for my Energy usage spreadsheets and I want to have a cell show the month prior to a certain date shown in another cell.
For example, say cell A1 is formatted with the Category of 'Date' (dd-mmm-yy) and has the value 01-Dec-22.
How could I arrange for cell A2 to show 'Nov'?
I can use =TEXT(A1,"mmm") in cell A2 to show Dec but I want it to show the previous month, Nov in this case.
Thanks.
For example, say cell A1 is formatted with the Category of 'Date' (dd-mmm-yy) and has the value 01-Dec-22.
How could I arrange for cell A2 to show 'Nov'?
I can use =TEXT(A1,"mmm") in cell A2 to show Dec but I want it to show the previous month, Nov in this case.
Thanks.
0
Comments
-
Not something I've tried, but the MONTH() function returns a number 1-12
Thus should be able to subtract [possibly a more complex formula so if the month value is 1, then it chooses 12 instead)
https://help.libreoffice.org/6.3/en-US/text/scalc/01/func_month.html?&DbPAR=CALC&System=UNIX
So I'd try
if(MONTH(A1)=1,12,MONTH(A1-1))Decluttering awards 2025: 🏅🏅⭐️ ⭐️, DH: ⭐️ and one for Mum: 🏅1 -
Try using edate
e.g.
=text(edate(A1;-1),"mmm")1 -
Thanks.
I've just tried entering =IF(MONTH(A1)=1,12,MONTH(A1-1)) in cell A2, formatted as Date, mmm and it returns Jan, which is the month after the date shown in A1. I want the month previous.
I'll have a look at your link.0 -
Ah yes, k_man, that works perfectly. Thank you.
All done now. Thanks to both of you for your time.0 -
I wonder if I could ask a follow up question.
Bearing in mind that the formula =text(edate(A1;-1),"mmm") correctly produces the result Nov when cell A1 contains 01-Dec-22 can anyone suggest how I could add the year to that result?
i.e. instead of just Nov showing, it shows Nov 22
Thanks.0 -
JohnB47 said:I wonder if I could ask a follow up question.
Bearing in mind that the formula =text(edate(A1;-1),"mmm") correctly produces the result Nov when cell A1 contains 01-Dec-22 can anyone suggest how I could add the year to that result?
i.e. instead of just Nov showing, it shows Nov 22
Thanks.=text(edate(A1;-1),"mmm yy")
Further info on the text function, and format options, including examples:
https://www.libreofficehelp.com/text-function/1 -
Thanks for that and for the link.
Well, that's interesting. When I use your formula, I get Nov 99
And when I expand the yy bit to yyyy I get 1899
Any further thoughts?
0 -
JohnB47 said:Thanks for that and for the link.
Well, that's interesting. When I use your formula, I get Nov 99
And when I expand the yy bit to yyyy I get 1899
Any further thoughts?
Try justedate(A1;-1)
This takes 1 month away from the date in A1.
Then change the format of the cell to bemmm yyRather thandd-mmm-yy (as for A1)
this assumes Libre office allows custom date formats for cells)1 -
Wonderful, that's it - works perfectly now.
The reason I want this is that It's in an 'Energy Usage' spreadsheet that I've created. I want to create a graph showing kWh usage over the months and because the graph spans two years, both the month and year are needed.
Many thanks for the help.0
Confirm your email address to Create Threads and Reply

Categories
- All Categories
- 350.9K Banking & Borrowing
- 253.1K Reduce Debt & Boost Income
- 453.5K Spending & Discounts
- 243.9K Work, Benefits & Business
- 598.7K Mortgages, Homes & Bills
- 176.9K Life & Family
- 257.2K Travel & Transport
- 1.5M Hobbies & Leisure
- 16.1K Discuss & Feedback
- 37.6K Read-Only Boards