Small question about LibreOffice Calc

JohnB47

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.

YBR

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))

k_man

Try using edate

e.g.

=text(edate(A1;-1),"mmm")

JohnB47

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.

JohnB47

Ah yes, k_man, that works perfectly. Thank you.

All done now. Thanks to both of you for your time.

JohnB47

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.

k_man

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.

Try

=text(edate(A1;-1),"mmm yy")

Further info on the text function, and format options, including examples:

https://www.libreofficehelp.com/text-function/

JohnB47

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?

k_man

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?

You may not need the text function at all, if the contents of A1 are in correct date format.

Try just

 edate(A1;-1)

This takes 1 month away from the date in A1.

Then change the format of the cell to be

mmm yy

Rather than

dd-mmm-yy (as for A1)

this assumes Libre office allows custom date formats for cells)

JohnB47

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.