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
JohnB47 Posts: 2,668 Forumite
Part of the Furniture 1,000 Posts Name Dropper
edited 19 December 2022 at 6:21PM in Techie Stuff
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.

Comments

  • YBR
    YBR Posts: 703 Forumite
    Seventh Anniversary 500 Posts Mortgage-free Glee! Name Dropper
    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: 🏅






  • k_man
    k_man Posts: 1,636 Forumite
    1,000 Posts Second Anniversary Name Dropper
    Try using edate

    e.g.

    =text(edate(A1;-1),"mmm")
  • JohnB47
    JohnB47 Posts: 2,668 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    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
    JohnB47 Posts: 2,668 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    edited 19 December 2022 at 6:46PM
    Ah yes, k_man, that works perfectly. Thank you.

    All done now. Thanks to both of you for your time.
  • JohnB47
    JohnB47 Posts: 2,668 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    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
    k_man Posts: 1,636 Forumite
    1,000 Posts Second Anniversary Name Dropper
    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
    JohnB47 Posts: 2,668 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    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
    k_man Posts: 1,636 Forumite
    1,000 Posts Second Anniversary Name Dropper
    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
    JohnB47 Posts: 2,668 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    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.
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

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

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.