LO Calc: Give last value for a given month

[SG777]

Hello! There’s a problem a just cannot solve and there’s nothing to be found in all the forums I’ve been searching for 2 days already… I hope it’s going to be an easy one for you guys, maybe it’s so trivial that nobody needed to ask before.
I’ve got a table like this:

A B
2020-01-02 32
2020-01-17 15
2020-02-10 36
2020-02-18 40
2020-02-25 67
2020-03-01 55
2020-03-10 70
etc.

All I want to do is to fetch the last value in B for january (15 in this example), february (67) etc. for use in another spreadsheet.
I spent 2 days trying everything from the internet, lookup, offset, match, but as if it wasn’t hard enough already, adding any date-magic makes this one impossible for me to grasp…
Really hope someone can help, many thanks in advance!

[Villeroy]

ask113828.ods (16.5 KB) (2nd version, calculating latest value for each month)

[SG777]

To be honest, I’m still trying to understand the syntax (every problem, especially those hard ones, are a chance to learning a little more) and adapting it to my case… But this is EXACTLY what I was looking for!
Thank you very much, sir, you are a saviour of lifetime, and a big thank you to everyone else, who gave me some moments of their time!

[fpy]

@Villeroy

calculating

can’t believe you didn’t strongly suggest select ... group by !?

[Lupp]

I wasn’t quite sure if “last” was meant as bottomnext entry (for the given month) or last (highest) date occurring for that month.
Also: The exmaple has monotonously ascending dates for each used month, but this property of the data set isn’t assured.
I made an additional example assuming “bottomnext entry” for the target row.
disask113828_lookupAssociatedValue_LastEntryForGivenMonth.ods (25.8 KB)

[Villeroy]

=INT((ROW(A1)-1)/12) in column J is a way to generate 12 times 0, 12 times 1, 12 times 2 etc. as often as you copy down. I add 2020 to this sequence.
=MOD(ROW(A1)-1;12)+1 in column K is a way to generate a sequence 1 to 12 as often as you copy down.
The formula in column L does the almost the same as the steps in column F.

• MAXIFS($A$2:$A$9;$A$2:$A$9;"<"&DATE($J2;$K2+1;1);$A$2:$A$9;">="&DATE($J2;$K2;1)) fetches the max from A2:A9 where A2:A9 is smaller than next month’s first day and larger than or equal to this month’s first day. DATE(y ; m ; d) calculates dates by 3 numbers year, month and day.
• VLOOKUP(MAXIFS(...);$A$2:$B$9;2;0) looks up this latest day in the source table’s first column and returns the value from the second column.

[SG777]

Thanks again, this I already copied in my little cheat book for future use and btw thanks for the little icing on my cake, this little trick (from my humble perspective almost magic) of generating the years and months is saving me time as I write
Everything translates perfectly to my sheet!

[Villeroy]

Actually, my first approach in column F was not quite accurate because the MAXIFS did not fetch the max. date for a month. It fetches the max date being smaller than the 1st of next month. If there happens to be no value for the month in question, it returns the latest value of a previous month, the 2 arguments $A$2:$A$9 ; ">="&DATE($F$2;$F$3;1) are missing in my first approach in column F.

[karolus]

see attached …ods
ask_113828.ods (15.8 KB)

[SG777]

First of all, thank you very much for your help an time. Sadly, it doesn’t work for me. As for my data
{=IF(MAXIFS( … )}
does nothing, not even an error message. As for the brackets, they just disappear, everything else stays as text.
Anyways, I wouldn’t know how to then take the value (in the example given 15 for january) and using it in another spreadsheet other than just manually link to Sheet1.C3. I was hoping that it wouldn’t be necessary to to this for all years and month past and future, since that pretty much defeats the purpose of the spreadsheet.
I very much hoped that there would be some solution like: for all 2020-01-* take the last value in column b, or for all dates <2020-02-01 take the youngest entry in b…

[koyotak]

Automatic date input in Calc with last day of month

I type date in format MM.YY and I want that Calc interpret that as date LAST DAY OF MONTH.MM.YYYY. I want chnge e.g 01.2026 to 31.01.2026. It’s ever possible? After some changes if I type MM.YY Calc change this in 01.MM.YYYY. I can’t force to change this. Thank you!

[karolus]

@koyotak: its NOT about calculating the End of month!

[fpy]

@SG777

last

another approach : How to group to months in Pivot Table?

[Villeroy]

@fpy

another approach : How to group to months in Pivot Table?

A grouped pivot table like this can be used to get a table of the latest dates (max. date per year and month) easier and then look up the corresponding values.
ask113828_2.ods (17.7 KB)