Month() in calc returns previous month

StonehouseTraveller · January 16, 2019, 3:38am

I have a column of dates and want to extract the month for sorting grouping purposes.

So in A2 I have 2018-01-01 formatted as NN, MMM DD YYYY I get Mon, Jan 01 2018

in B2 fI have =weekday(a2) and get Monday

in C3 I have = month(a2) and get December

when the month changes to February, I get month() = January

For expediency I have changed the formula in C2 to be =month(A2)+1 and get the right month.

My question is, what am I doing wrong in this case. It’s always worked before.

Any hints would be appreciated.

Paul

StonehouseTraveller · January 16, 2019, 3:46am

Sorry Windows 10 Libreoffice version 6.0.4.2 (x64)

JohnSUN · January 16, 2019, 5:15am

I was able to reproduce this behavior. I used your formulas exactly as described. The problem was that the date and these formulas were written in row 1, the formulas referred to the cell in the next row. I changed the address of the cells in the functions and got the right results.Perhaps you made the same mistake?

mikekaganski · January 16, 2019, 5:25am

@JohnSUN: I’m afraid that in that case, it couldn’t happen that

when the month changes to February, I get month() = January

@StonehouseTraveller: a sample file would be useful.

JohnSUN · January 16, 2019, 6:12am

@mikekaganski I could repeat this behavior too - column A is filled with formulas like = A1 + 1. In this case, changing the date in the first cell will indeed change all subsequent dates (and MONTH() of them). The author of the question did not say anything about cell A3, A4, etc. Do you see? In question he use A2, B2 and C3 This is the reason why I immediately thought of the addresses of the cells and the number of rows.

mikekaganski · January 16, 2019, 7:10am

I see your point; but now I think that @libreofficeUser30872 gave absolutely correct answer, that OP has formatted the results of WEEKDAY() and MONTH() using some date formatting like NNN and MMM, which interpreted 2 (returned by WEEKDAY) as date “1900-01-01” which was Monday, and 1 (returned by MONTH) as “1899-12-31” (December).

StonehouseTraveller · January 18, 2019, 5:47pm

Thank you all for your help. I see the error in my ways. Having extracted the “month” from the date in A2 (=month(a2)), I then proceeded to format it with a date format MMMM. When I convert to a number I get correct numeric values. I guess it was actually looking for date of “1” (January conversion) and finding some unknown December date.

Once it followed your advice and simply made it equal to A2, then the MMMM format worked like a charm. Alternatively I guess I could have done =choose(a2,January, Febru…) but your solution is more elegant.