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: 46376
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
Hatfields & McCoys Sensationalist Account: An American Vendetta
Humor: Maxims of Methuselah about women

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: 46376
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.
Hatfields & McCoys Sensationalist Account: An American Vendetta
Humor: Maxims of Methuselah about women

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: 46376
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!
Hatfields & McCoys Sensationalist Account: An American Vendetta
Humor: Maxims of Methuselah about women

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: 46376
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!!
Hatfields & McCoys Sensationalist Account: An American Vendetta
Humor: Maxims of Methuselah about women

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: 46376
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-)
Hatfields & McCoys Sensationalist Account: An American Vendetta
Humor: Maxims of Methuselah about women

CSCO
Posts: 212
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.
!!!!.!!!!!!.!!!!.!!!!!!!!!..!!!.!!!!!!!!!!!...!!!!!.!!!!!!.!!!!!!!!.!!!!!!.!!!!!!

ZamesCurran
Posts: 467
Joined: March 2nd, 2015, 9:08 am
Location: Bloomfield, NJ
Contact:

Post by ZamesCurran » February 28th, 2020, 9:24 am

OK, so I'm a little late with this.

For LibreCalc:

(Assuming you don't already have macros attached to the spreadsheet)
1. Open your spreadsheet
2. Tools/Marco/Organize Macros/LibreOffice Basic
3. CLick "New" on dialog.
4. In the editor that opens, paste in the following after the "Sub Main / End Main"

Code: Select all

Function DOWofMonth(year As Integer, month As Integer, num As Integer, dow As String) As Date
    Dim som As Date
    Dim sdow As Integer
    Dim offset As Integer
    Dim dy  As Date
    Dim downum
    
    downum = InStr("x|SU|MO|TU|WE|TH|FR|SA", Left(UCase(dow), 2)) / 3
    
    som = DateSerial(year, month, 1)
    
    sdow = Weekday(som, 1)
    offset = downum - sdow
    If offset = 0 Then
        num = num - 1
    End If
    DOWofMonth = DateAdd("d", offset + num * 7, som)
End Function

Then in your spreadsheet you can use
=DOWofMonth(2020, 1, 3, "Wednesday")

for 3rd Wednesday of Jan 2020.

Paramters are
Year (integer)
Month (integer)
count (integer)
Day of week (string, case ignored, only first two letters used)
Truth,
James
---------------------

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

Post by TriciaG » February 28th, 2020, 12:19 pm

Thanks, and apologies! I should have marked this thread as answered.

I think I've already figured out the "3rd Thursday" problem. This has dropped to the wayside now because I can't get it to display more than one event on a date. If there's more than one birthday, anniversary, or other event, it only displays the first one it encounters and not the next one(s).

I haven't taken the time to figure out how to fix this current issue.
Hatfields & McCoys Sensationalist Account: An American Vendetta
Humor: Maxims of Methuselah about women

Post Reply