Way Off Topic: Spreadsheet Formula Help!

Everything except LibriVox (yes, this is where knitting gets discussed. Now includes non-LV Volunteers Wanted projects)
Post Reply
TriciaG
LibriVox Admin Team
Posts: 44179
Joined: June 15th, 2008, 10:30 pm
Location: Toronto, ON (but Minnesotan to age 32)

Post by TriciaG » January 24th, 2020, 10:15 am

Do we have any spreadsheet gurus here?

I'm working on a "universal calendar" in which I can change the year, and it'll spit out monthly calendars with all the holidays, birthdays, anniversaries, etc. filled in. I've started with a LibreOffice Calc template I found on their site, but I'm getting stuck on some formulas for certain events.

Here are the formulas for Labor Day (first Monday of September):
=DATE(Year,9,1)+IF(2<WEEKDAY(DATE(Year,9,1)),7-WEEKDAY(DATE(Year,9,1))+2,2-WEEKDAY(DATE(Year,9,1)))+((1-1)*7)

and Memorial Day (4th Monday of May):
=DATE(Year,6,IF(2<WEEKDAY(DATE(Year,6,1)),10-WEEKDAY(DATE(Year,6,1)),3-WEEKDAY(DATE(Year,6,1)))-7)

Using these same formats, what would be the formula for
- The first Sunday of each month (I assume each month would need a separate formula)?
- The 3rd Sunday of each month?
- Every Wednesday?

Is there a good way to generalize the formula so I can digest it better and not have to ask for someone to make them for me? :) I have a bunch to add in (Canadian holidays that aren't US ones, for example).

If it helps, the template I am using is here: https://extensions.libreoffice.org/templates/calendar-creator
Mystery stories: The Master of Mysteries
Kerner Report on 1967 race riots: LINK
Mussolini's speeches thru 1923: LINK
The Medici family history: LINK

GrayHouse
Posts: 505
Joined: October 6th, 2012, 3:27 pm

Post by GrayHouse » January 24th, 2020, 11:01 am

This is the usual generic method that most people use.

For 'every Wednesday' I'd start with an arbitary Wednesday as your starting point and just add 7 each time. You know that spreadsheets represent dates as integer days (with decimals for the time) so you can just manipulate them arithmetically which is often simpler than using the date functions.

Does that get you closer to a solution?

-Ian

TriciaG
LibriVox Admin Team
Posts: 44179
Joined: June 15th, 2008, 10:30 pm
Location: Toronto, ON (but Minnesotan to age 32)

Post by TriciaG » January 24th, 2020, 11:31 am

That solves the "every Wednesday" thing, yes. But it doesn't get me a generic way to get the first Sunday of the month, because I don't have a field where it determines what is the first day of the month in that given year. At least, I don't THINK I have that. The spreadsheet is very confusing! But I like it, because I can enter all the dates I want on a list, and it'll plug them into each month (a separate sheet) for me. Once I get a formula for, say, the first Sunday of January, I won't have to manipulate the calculation when I change the year.

That formula in the link you gave me starts with a date in B5 - where that date came from is anyone's guess; it looks arbitrary.

Maybe it will work, and I have to just study it out more. :hmm: My brain used to be good at stuff like this; now it feels mushy.
Mystery stories: The Master of Mysteries
Kerner Report on 1967 race riots: LINK
Mussolini's speeches thru 1923: LINK
The Medici family history: LINK

GrayHouse
Posts: 505
Joined: October 6th, 2012, 3:27 pm

Post by GrayHouse » January 24th, 2020, 12:10 pm

Unfortunately I'm away from home and I don't have a way of opening OpenDocument Templates, otherwise I'd be happy to program it for you.
If you have access to the year and month of the calendar you're creating - presumably they're entered somewhere - then you can use them to create the 'B5' date, or incorporate it in to the formula. So you could modify the formula to be something like:
DATE(myYear, myMonth, 1)-DAY(DATE(myYear, myMonth, 1))+1+n*7-WEEKDAY(DATE(myYear, myMonth, 1)-DAY(DATE(myYear, myMonth, 1))+8-dow)
where myYear and myMonth are references to the appropriate calendar values.
I may be missing something because I can't see the sheet you're working with...
-Ian

TriciaG
LibriVox Admin Team
Posts: 44179
Joined: June 15th, 2008, 10:30 pm
Location: Toronto, ON (but Minnesotan to age 32)

Post by TriciaG » January 24th, 2020, 1:31 pm

I THINK it's working! It worked for Victoria Day (roughly, 3rd Monday May) and Family Day (3rd Monday February). I'll try it on some others.

THANKS!
Mystery stories: The Master of Mysteries
Kerner Report on 1967 race riots: LINK
Mussolini's speeches thru 1923: LINK
The Medici family history: LINK

GrayHouse
Posts: 505
Joined: October 6th, 2012, 3:27 pm

Post by GrayHouse » January 24th, 2020, 1:39 pm

We can all identify with that frisson of joy when one's program works slightly against expectations... :clap:

TriciaG
LibriVox Admin Team
Posts: 44179
Joined: June 15th, 2008, 10:30 pm
Location: Toronto, ON (but Minnesotan to age 32)

Post by TriciaG » January 24th, 2020, 1:57 pm

"Slightly" is right. That part's working, but values aren't getting displayed on the appropriate monthly calendar when there is more than one match. (If there's a birthday AND something else - even two birthdays - only the first match gets displayed.)

No wonder the only software I found for anything remotely like this costs either an arm and a leg, or a monthly subscription. It's very complicated!!
Mystery stories: The Master of Mysteries
Kerner Report on 1967 race riots: LINK
Mussolini's speeches thru 1923: LINK
The Medici family history: LINK

GrayHouse
Posts: 505
Joined: October 6th, 2012, 3:27 pm

Post by GrayHouse » January 24th, 2020, 2:39 pm

That may be a limitation of the program; it's hard to tell without looking at the code. I guess it's using the usual lookup/match functions which only return the first matching value in a list. It's possible the person who wrote it didn't anticipate more than one matching date per calendar day. Unfortunately I won't be able to look at the code for quite some time. Perhaps you could request a fix from the author. Sorry I can't be more helpful right now.

TriciaG
LibriVox Admin Team
Posts: 44179
Joined: June 15th, 2008, 10:30 pm
Location: Toronto, ON (but Minnesotan to age 32)

Post by TriciaG » January 24th, 2020, 2:44 pm

That's fine. I thank you heartily for the help you've already provided! 8-)
Mystery stories: The Master of Mysteries
Kerner Report on 1967 race riots: LINK
Mussolini's speeches thru 1923: LINK
The Medici family history: LINK

CSCO
Posts: 145
Joined: April 6th, 2010, 10:48 am
Location: Toyokawa, Japan

Post by CSCO » January 27th, 2020, 1:57 am

Hi,

If it is a leap year, September 1 and January 7 have the same weekday.
(If not, September 1 and January 6 have the same weekday.)

CSCO discovered that in 2020. It's looking up a calendar in your hand method.


P.P. (CSCO)
!!!!!!.!!!!!!.!!!!.!!!!!!!!!..!!!.!!!!!!!!!!!...!!!!!!!!!.!!!!!!.!!!!.!!!!!!.!!!!
No way. He stole away a pretty thing, you know.
That's your heart.
!!!!.!!!!!!.!!!!.!!!!!!!!!..!!!.!!!!!!!!!!!...!!!!!.!!!!!!.!!!!!!!!.!!!!!!.!!!!!!

Post Reply