0

I have a spreadsheet with three columns for "Date", "Currency", and "Amount".

Date        Currency    Amount  Month
06.02.2015  EUR         188     March
30.01.2015  EUR         285     January

I create the "Month" column from the Date-Column using the following formula and then formatting the "Month" column to MMMM:

=DATE(YEAR(A2);MONTH(A2);DAY("0"))

(I do this because I need to group the data in a Pivot-Table by month.)

The Problem: As you can see from the excerpt, the month returned for the first value for month is incorrect. It says "March", when it really is "February". What am I doing wrong here?

For the first month-value (or values, depending on how many entries there are for the first listed month) in the spreadsheet, libreCalc always seems to count one month ahead. The remaining values, starting from the next month, are always correct and work just fine. It makes no difference whether the months are listed January-December or the other way around, as in my example.

Thanks for helping!

  • Still no answers... Is this the right forum for the question? – kat Mar 5 '15 at 7:15
1

I believe the problem is that the function DAY("0") returns the answer 30. Since February has less than 30 days, this causes a problem for dates in February.

Entering just a 0 for the third argument of the DATE function causes it to return the last day of the previous month, which is also not what you seem to want. I'd recommend using a 1 for the third argument of the DATE function:

=DATE(YEAR(A2);MONTH(A2);1)
  • 1
    Your solution works, but you got the explanation a little wrong. Day returns the day of the month for a given date. If instead of using a date as the argument, you use a number (which should exclude the text quotes, but Calc is forgiving), it is taken as the date that many days from the base date of December 30, 1899. So day 0 is the 30th day of December, 1899. Hence, the 30 result. The argument should be either 1, as you suggested, or DAY(A2). But kudos for recognizing the problem and suggesting a working solution. +1 – fixer1234 Apr 10 '15 at 1:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.