Calc =MONTH problem

users
#1

Hello

Not sure if I'm doing something wrong, if theres a setting I haven't set or
I've found a bug

Libreoffice Calc
Program settings are >>Options>>Language Settings>>Languages
User interface 'English (uk)'
Date acceptance patterns 'D/M/Y;D/M;D-M'

And the problem is
In a cell (CF3) I have the date >> 01/09/2013 which is DD/MM/YYYY
In another cell I have =MONTH(CF3) this cell is formatted as 'date' 'MMM'
and I expect the cell to show 'Sep' it actually shows 'Jan'
So its showing the day as the month and not the month as the month

Any ideas how to fix

#2

Hi.
This works correctly for me (LO 3.6)
If you enter 13/09/2013, just check it shows 13/09/2013.
What is the locale setting on your PC.
Steve

#3

Not sure if I'm doing something wrong, if there's a setting I haven't set or I've found a bug

I fear you are doing something wrong!

Libreoffice Calc
Program settings are >>Options>>Language Settings>>Languages
User interface 'English (uk)'
Date acceptance patterns 'D/M/Y;D/M;D-M'

And the problem is
In a cell (CF3) I have the date >> 01/09/2013 which is DD/MM/YYYY
In another cell I have =MONTH(CF3) this cell is formatted as 'date' 'MMM' and I expect the cell to show 'Sep' it actually shows 'Jan'
So it's showing the day as the month and not the month as the month

No, it's not showing the day as the month. You can see this if you change the date from 1 to 2 September: your result will still be "Jan", not "Feb".

You are doing too much work. When you extract the month using the MONTH() function, you get the result 9 (for September). So far so good. But you cannot simply display the month name by formatting this value as a date, since it is no longer a proper date value. As a date, the value 9 means nine days from day zero - probably 8 January 1900. You will see that "Jan" is the correct MMM display for this date.

Any ideas how to fix

Yes. The solution is simply to format *the original date value* (or a copy of it) as an MMM date. Alternatively, you could set up an array of text values - "Jan", "Feb", and so on - and then use your month integer as an index to retrieve the correct month name from the array.

I trust this helps.

Brian Barker

#4

Hello and thanks for looking at the problem

So my understanding and logic was wrong - again I have learnt a smidgen more

Eventually got what I wanted by using =TEXT(CG4,"MMM")
I had thought of using an array but It seemed a big way of solving a small
problem - plus I'd never used one before ?? Maybe I should have just for the
'smidgen more'

Again, thanks for the help

#5

Interestingly, this gives a different result from the formatting option. The results look the same, but mine was a formatted date (which will be right-aligned by default) and this is an actual text string (which will be left-aligned). But any further calculations on these values will differ. And they will sort differently: January dates come before February ones, but "Feb" comes before "Jan".

Horses for courses.

Brian Barker