Way Off Topic: Spreadsheet Formula Help!

[TriciaG]

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

[GrayHouse]

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]

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. My brain used to be good at stuff like this; now it feels mushy.

[GrayHouse]

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]

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!

[GrayHouse]

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

[TriciaG]

"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!!

[GrayHouse]

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]

That's fine. I thank you heartily for the help you've already provided!

[CSCO]

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)

[ZamesCurran]

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)

[TriciaG]

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.