Ask Your Question
1

Month() in calc returns previous month

asked 2019-01-16 04:38:15 +0200

StonehouseTraveller gravatar image
  1. 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

edit retag flag offensive close merge delete

Comments

Sorry Windows 10 Libreoffice version 6.0.4.2 (x64)

StonehouseTraveller gravatar imageStonehouseTraveller ( 2019-01-16 04:46:53 +0200 )edit

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?

JohnSUN gravatar imageJohnSUN ( 2019-01-16 06:15:27 +0200 )edit

@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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-01-16 06:25:28 +0200 )edit

@Mike Kaganski 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.

JohnSUN gravatar imageJohnSUN ( 2019-01-16 07:12:55 +0200 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2019-01-16 08:10:31 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2019-01-18 18:47:26 +0200

StonehouseTraveller gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-01-16 04:38:15 +0200

Seen: 56 times

Last updated: Jan 18