Outlook Calendar Holidays 2

[herbstgy]

Dear All,

is it possible to read from the Outlook Application object if a date is holiday according to the local national settings?
I'm thinking of an IsHoliday(DATE()) function which would simply return .t. or .f.

 

[Scott24x7]

Outlook doesn't have a way to do this directly, because when "holiday" items are applied in outlook, they are just brought in as calendar items like any other calendar item. (Outlook doesn't default holidays, you have to import them). That said, if you have some criteria that allows you to identify it as a holiday, you could do this:

Iterate Through Calendar Items: For the date in question, iterate through calendar items to check if any are marked in a way that you can pre-define as indicating a holiday (e.g., subject contains "Holiday", all-day event, etc.).

Determine Holiday Status: Based on your criteria, determine if the date is a holiday and return .t. (true) or .f. (false).

LOCAL loOutlook, loNamespace, loFolder, loItems, ldDate
ldDate = DATE()  && Example date

* Create Outlook application object
loOutlook = CREATEOBJECT("Outlook.Application")
loNamespace = loOutlook.GetNameSpace("MAPI")

* Access the Calendar folder (default calendar)
loFolder = loNamespace.GetDefaultFolder(9)  && 9 corresponds to olFolderCalendar
loItems = loFolder.Items

* Now you would filter or iterate through loItems to find if there's a holiday
* This part of the logic needs to be developed based on your criteria for identifying holidays

RELEASE loOutlook

Keep in mind:
This example does not directly implement a check for holidays. You need to expand upon the concept to filter and check calendar items.
Accessing Outlook from VFP9 via COM automation might require adjustments based on your specific environment and Outlook version.

Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCAP, CDCP, CDCS, CDCE, CTDC, CTIA, ATS, ATD

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Chris Miller]

Thanks for pointing that out. I only found tons of sites telling you how to add national holidays into Outlook. I think they all put in items into one new separate calendar so knowing that calendar would mean all its items are holidays. The other information you find is that Outlook imports all these national holidays from .hol files, of which there are downloads, too, with holidays up to this year or 2026, likely Microsoft keeps upgrading them when you have Office. I don't find the official MS source for them, that you could milk instead of Outlook. Well, I understand you don't really want to decide which countries holidays, that's upp to the user or admin of his company.

Edit: I'm wrong about holidays being added to a separate calendar, referring to this instruction video by MS the holidays you can add (optionally) will become items of your own calendar:

https://support.microsoft.com/en-us...calendar-6d43a892-4f9d-4158-a742-78f13c4e0aca

And the latest information about that still says it's writing holidays into "your calendar":

https://support.microsoft.com/en-us...-windows-f16d872f-8dad-4750-bb7c-166e72c26977

Albeit, one paragraph there also states:

If you have a work or school account in Microsoft 365, you can subscribe to a holidays calendar using Outlook on the web and receive automatic updates when holidays are added or changed.

So that's speaking of a separate holidays calendar again.

I think that means different options for different Outlook versions and usage of web application or desktop application, including that even older versions may handle holidays as separate calendar or items in your personal calendar.
I don't use Outlook for myself and won't install that just to check, but it looks like there's no easy one size fits all solution, as good as the thought is to not annoy users to need to feed in their holidays into your application, if they already did feed their Outlook calendar.

Scotts and Mikes code should help you find items and maybe find a way to determine how they identify as holidays.

Chriss

 

[Mike Lewis]

Of course, you can also flag holidays from within FoxPro. There's an article about that on my site: Generate a table of "notable dates". But the method described depends on maintaining a rule table (e.g. "Holiday X falls on the second Monday in May"). A method that is sensitive to the user's locale would be better. As far as I know, there is no way of doing that, as Windows doesn't maintain holiday information.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[herbstgy]

Thanks, Scott, I can go on with this.

 

[vernpace]

We use this for DBI's ctxCalendar Control. Has worked perfectly over the years... You will need to modify it to suit your needs:

LPARAMETERS dtdate
LOCAL liMonth, liYear, liCount, liDay

liMonth = MONTH(dtdate)
liYear = YEAR(dtdate)

DO CASE
   CASE liMonth = 1
        IF TTOD(dtdate) = DATE(liYear, liMonth, 1)
           This.DateHeaderText = "New Years Day"
        ENDIF

        IF DOW(DATE(liYear, liMonth, 1)) = 1 AND (DATE(liYear, liMonth, 1) + 1 = TTOD(dtdate))
           This.DateHeaderText = "New Years Day (Observed)"
        ENDIF

        liCount = 0

        FOR liDay = 1 TO 31
            IF DOW(DATE(liYear, liMonth, liDay)) = 2
               liCount = liCount + 1

               IF liCount = 3
                  EXIT
               ENDIF
            ENDIF
        ENDFOR

        IF TTOD(dtdate) = DATE(liYear, liMonth, liDay)
           This.DateHeaderText = "Martin Luther King Day"
        ENDIF

   CASE liMonth = 2
        liCount = 0

        FOR liDay = 1 TO 28
            IF DOW(DATE(liYear, liMonth, liDay)) = 2
               liCount = liCount + 1

               IF liCount = 3
                  EXIT
               ENDIF
            ENDIF
        ENDFOR

        IF TTOD(dtdate) = DATE(liYear, liMonth, liDay)
           This.DateHeaderText = "Presidents Day"
        ENDIF

   CASE liMonth = 5
        FOR liDay = 31 TO 1 STEP -1
            IF DOW(DATE(liYear, liMonth, liDay)) = 2
               EXIT
            ENDIF
        ENDFOR

        IF TTOD(dtdate) = DATE(liYear, liMonth, liDay)
           This.DateHeaderText = "Memorial Day"
        ENDIF

   CASE liMonth = 6
        IF TTOD(dtdate) = DATE(liYear, liMonth, 19)
           This.DateHeaderText = "Juneteenth"
        ENDIF

        DO CASE
           CASE DOW(DATE(liYear, liMonth, 19)) = 7 AND (DATE(liYear, liMonth, 19) - 1 = TTOD(dtdate))
                This.DateHeaderText = "Juneteenth (Observed)"

           CASE DOW(DATE(liYear, liMonth, 19)) = 1 AND (DATE(liYear, liMonth, 19) + 1 = TTOD(dtdate))
                This.DateHeaderText = "Juneteenth (Observed)"

        ENDCASE

   CASE liMonth = 7
        IF TTOD(dtdate) = DATE(liYear, liMonth, 4)
           This.DateHeaderText = "Independence Day"
        ENDIF

        DO CASE
           CASE DOW(DATE(liYear, liMonth, 4)) = 7 AND (DATE(liYear, liMonth, 4) - 1 = TTOD(dtdate))
                This.DateHeaderText = "Independence Day (Observed)"

           CASE DOW(DATE(liYear, liMonth, 4)) = 1 AND (DATE(liYear, liMonth, 4) + 1 = TTOD(dtdate))
                This.DateHeaderText = "Independence Day (Observed)"

        ENDCASE

   CASE liMonth = 9
        FOR liDay = 1 TO 30
            IF DOW(DATE(liYear, liMonth, liDay)) = 2
               EXIT
            ENDIF
        ENDFOR

        IF TTOD(dtdate) = DATE(liYear, liMonth, liDay)
           This.DateHeaderText = "Labor Day"
        ENDIF

   CASE liMonth = 10
        liCount = 0

        FOR liDay = 1 TO 31
            IF DOW(DATE(liYear, liMonth, liDay)) = 2
               liCount = liCount + 1

               IF liCount = 2
                  EXIT
               ENDIF
            ENDIF
        ENDFOR

        IF TTOD(dtdate) = DATE(liYear, liMonth, liDay)
           This.DateHeaderText = "Columbus Day"
        ENDIF

   CASE liMonth = 11
        IF TTOD(dtdate) = DATE(liYear, liMonth, 11)
           This.DateHeaderText = "Veterans Day"
        ENDIF

        DO CASE
           CASE DOW(DATE(liYear, liMonth, 11)) = 7 AND (DATE(liYear, liMonth, 11) - 1 = TTOD(dtdate))
                This.DateHeaderText = "Veterans Day (Observed)"

           CASE DOW(DATE(liYear, liMonth, 11)) = 1 AND (DATE(liYear, liMonth, 11) + 1 = TTOD(dtdate))
                This.DateHeaderText = "Veterans Day (Observed)"

        ENDCASE

        liCount = 0

        FOR liDay = 1 TO 30
            IF DOW(DATE(liYear, liMonth, liDay)) = 5
               liCount = liCount + 1

               IF liCount = 4
                  EXIT
               ENDIF
            ENDIF
        ENDFOR

        IF TTOD(dtdate) = DATE(liYear, liMonth, liDay)
           This.DateHeaderText = "Thanksgiving Day"
        ENDIF

   CASE liMonth = 12
        IF TTOD(dtdate) = DATE(liYear, liMonth, 25)
           This.DateHeaderText = "Christmas Day"
        ENDIF

        DO CASE
           CASE DOW(DATE(liYear, liMonth, 25)) = 7 AND (DATE(liYear, liMonth, 25) - 1 = TTOD(dtdate))
                This.DateHeaderText = "Christmas Day (Observed)"

           CASE DOW(DATE(liYear, liMonth, 25)) = 1 AND (DATE(liYear, liMonth, 25) + 1 = TTOD(dtdate))
                This.DateHeaderText = "Christmas Day (Observed)"

        ENDCASE

        IF DOW(DATE((liYear + 1), 1, 1)) = 7 AND (DATE((liYear + 1), 1, 1) - 1 = TTOD(dtdate))
           This.DateHeaderText = "New Years Day (Observed)"
        ENDIF

ENDCASE

 

[Chris Miller]

vernpace,

Do you understand now, why herbstgy wants to get the holidays from the Outlook calendar?

Chriss

 

[vernpace]

Chris,

Ah, I missed that. The interesting question is how Outlook renders the holidays in other countries - based on locality?. For instance, if Vietnam is selected, are the holidays rendered in Vietnamese or English?

 

[Chris Miller]

Windows and also Outlook work based on Language packs, so what do you think?

They (I mean Microsoft, of course) do have the ressources to populate them in whatever is the language Windows is configured to use. Since this is all data it could well be all that or just regional and English, finally I don't know, but any text of Windows - even special folders, are translated by the Windows language packs, all settings forms, etc, etc.

That the titles of holiday calendar items are unknown establishes the difficulty of categorising the calendar items. Their title will likely not start 'Holiday' for everyone everywhere. If items are created in a specific calendar for holidays, that could have the technical name 'holidays', that's usually not localized. Also categories of items may be available in English. Despite efforts in Excel to localize even the VBA functions, that's usually all in English, just display names are localized, and so the technical categories of calendar items (I think something like that is also mentioned in the demonstration video), their display names are localized, but code should get at the englich categories, or category constants.

Aside from all that, it's astonishing that MS offers this feature for Outlook, but does not maintain holidays on the level of Windows itself. When these informations exist to feed Outlook calendars, it is obviously there and MS would be able to add this to other regional Windows settings and thus in the Windows calendar.

What else I remebered is that a Holiday API is among the getting started samples of the XML Web Services of the Task Pane, but I might confuse this with something else. The Soap Toolkit is outdated anyway.

Chriss

 

[atlopes]

herbstgy,

Once you export the Outlook calendar in iCalendar format, you can use the iCal4VFP library (at

https://github.com/atlopes/iCal4VFP)

to ingest the events dates into a cursor and work from there.

This is the basic code to export the calendar from Outlook and import it into a VFP cursor.

[b][COLOR=#4E9A06]*
* read the calendar events from Outlook
* and insert into a cursor, using the iCal4VFP library
*[/color][/b]

DO LOCFILE("iCalLoader.prg")

LOCAL iCsProc AS ICSProcessor
LOCAL iCal AS iCalendar

[b][COLOR=#4E9A06]* create an ICS format processor[/color][/b]
m.iCsProc = CREATEOBJECT("ICSProcessor")

[b][COLOR=#4E9A06]* export the Calendar from Outlook in iCalendar format[/color][/b]
LOCAL Outlook AS Outlook.Application
LOCAL NS AS Outlook.Namespace
LOCAL Folder AS Outlook.Folder
LOCAL ExpCal AS Outlook.CalendarSharing
LOCAL OutlookICS AS String

m.Outlook = CREATEOBJECT("Outlook.Application")
m.NS = m.Outlook.GetNamespace("MAPI")
m.Folder = m.NS.GetDefaultFolder(9)		[b][COLOR=#4E9A06]&& calendar[/color][/b]
m.ExpCal = m.Folder.GetCalendarExporter()

m.ExpCal.CalendarDetail = 2	[b][COLOR=#4E9A06]&& full details[/color][/b]
m.ExpCal.StartDate = DATE(YEAR(DATE()), 1, 1)
m.ExpCal.EndDate = DATE(YEAR(DATE()), 12, 31)
m.ExpCal.IncludeWholeCalendar = .T.

m.OutlookICS = ADDBS(SYS(2023)) + "~outlook.ics"

m.ExpCal.SaveAsICal(m.OutlookICS)

STORE .NULL. TO ;
	m.ExpCal, m.Folder, m.NS, m.Outlook

[b][COLOR=#4E9A06]* read the holidays/events definitions[/color][/b]
m.iCal = m.iCsProc.ReadFile(m.OutlookICS)

[b][COLOR=#4E9A06]* prepare a cursor to get all events[/color][/b]
CREATE CURSOR CalEvents (eventDate date, eventType Varchar(64), eventName varchar(64))

INDEX ON eventDate TAG eventDate

LOCAL CalEvent AS ICComponent
LOCAL EventIndex AS Integer
LOCAL ARRAY Categories[1]

[b][COLOR=#4E9A06]* for every event in the calendar[/color][/b]
FOR m.EventIndex = 1 TO m.iCal.GetICComponentsCount("VEVENT")

	m.CalEvent = m.iCal.GetICComponent("VEVENT", m.EventIndex)

	m.Categories = m.CalEvent.GetICPropertyValue("CATEGORIES")

	[b][COLOR=#4E9A06]* insert in our cursor, for future use[/color][/b]
	INSERT INTO CalEvents (eventDate, eventType, eventName) ;
		VALUES (m.CalEvent.GetICPropertyValue("DTSTART"), ;
			m.Categories[1], ;
			m.CalEvent.GetICPropertyValue("SUMMARY"))

ENDFOR

[b][COLOR=#4E9A06]* browse the results[/color][/b]
SELECT CalEvents
GO TOP
BROWSE

The result is as follows. Note that category names and event descriptions are localized. In Portuguese, "feriado" means holiday, and "Sexta-feira Santa" (for instance) is "Good Friday".

If you're allowed to consider other sources for the definition of holidays, plenty of resources are available, and many also use the iCalendar format.

The following code reads an iCalendar file that defines US national holidays, using Recurrence Rules, a syntax to express periodical and repetitive events in time.

For instance, this is the definition of Martin Luther King Jr's day:

BEGIN:VEVENT
DTSTART;VALUE=DATE:19860120
RRULE:FREQ=YEARLY;BYMONTH=1;BYDAY=MO;BYSETPOS=3
SUMMARY:Martin Luther King Jr. Day (U.S.)
UID:20111111000000Z-US_COMMON_HOLIDAYS-02@sappjw
END:VEVENT

This definition reads as every year, on the third Monday of January, starting at {^1986-01-20}.

The iCal4VFP integrates a processor that can calculate a set of dates defined by these Recurrence Rules.

[b][COLOR=#73D216]*
* read the calendar events from an iCalendar file
* and insert them into a cursor, using the iCal4VFP library
*[/color][/b]
DO LOCFILE("iCalLoader.prg")

LOCAL iCsProc AS ICSProcessor
LOCAL iCal AS iCalendar

[b][COLOR=#73D216]* create an ICS format processor[/color][/b]
m.iCsProc = CREATEOBJECT("ICSProcessor")

[b][COLOR=#73D216]* read the holidays definitions[/color][/b]
m.iCal = m.iCsProc.ReadUrl("[URL unfurl="true"]https://github.com/sappjw/calendars/raw/master/US_Holidays.ics")[/URL]

[b][COLOR=#73D216]* prepare a cursor to get all events[/color][/b]
CREATE CURSOR USEvents (eventDate date, eventName varchar(64))

INDEX ON eventDate TAG eventDate

LOCAL USEvent AS ICComponent
LOCAL EventRule AS ICProperty

LOCAL EventIndex AS Integer
LOCAL RuleIndex AS Integer
LOCAL RuleCursor AS String

[b][COLOR=#73D216]* for every event (from New Year's day to Christmas)[/color][/b]
FOR m.EventIndex = 1 TO m.iCal.GetICComponentsCount("VEVENT")

	m.USEvent = m.iCal.GetICComponent("VEVENT", m.EventIndex)

	[b][COLOR=#73D216]* for every rule that defines it (usually, just one)[/color][/b]
	FOR m.RuleIndex = 1 TO m.USEvent.GetICPropertiesCount("RRULE")

		m.EventRule = m.USEvent.GetICProperty("RRULE", m.RuleIndex)

		[b][COLOR=#73D216]* calculate all recurrences since its start date through all 21st. century[/color][/b]
		m.RuleCursor = m.EventRule.CalculateAll(m.USEvent.GetICPropertyValue("DTSTART"), {^2100-12-31})

		[b][COLOR=#73D216]* insert in our cursor, for future use[/color][/b]
		INSERT INTO USEvents (eventDate, eventName) ;
			SELECT DISTINCT localTime, CAST(m.USEvent.GetICPropertyValue("SUMMARY") AS Varchar(64)) ;
				FROM (m.RuleCursor)

		USE IN SELECT(m.RuleCursor)

	ENDFOR

ENDFOR

[b][COLOR=#73D216]* browse the results[/color][/b]
SELECT USEvents
GO TOP
BROWSE

The code generates all holidays from their start date and for all 21st century (currently, 3032 events). A fraction of the result, around the middle of the century:

[highlight #73D216][/highlight]

 

[Mike Lewis]

A small digression, if I may ....

I noticed that António's screen shot showed many holidays in the USA. My understanding is that there are only four US national holidays (Martin Luther King Day, Memorial Day, Independence Day, Labor Day). But many other holidays are observed at the state level. Is that right?

For example, today (19 Feb) is the third Monday in February. Many states observe this as Presidents Day. But many don't. When I lived in California, we had Washington's Birthday and Lincoln's Birthday quite close together in February, but these now seem to be merged into one holiday, which is today, but is not called Presidents Day (as far as I know). Some states still have Washington's birthday, but not necessarily today. Some (Delaware?) do not observe a holiday at all around now.

I also noticed that the screen shot showed Valentine's Day, Groundhog Day, and some others. Surely these aren't holidays anywhere?

Any Americans reading this might take a moment out from their Presidents Day celebrations to let me know if I've understood all this correctly.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Mike,

sorry, I can't confirm in detail, but generally speaking, not only the US has diversions from list of national holidays in each state. And some of them are still work days, too. So Outlooks level of countries surely doesn'T cover that in detail. I just think a company might base its holiday/vacation management on a shared outlook calendar. And you also would have holidays in such a calender, if they are work days in your region/state, as it may be good information to know offices or customers or other companies have them as hoidays.

Whatever, Wikipeadia has an exhaustive list of Holidays mostly with links to their own Wikipeadia page.

https://en.wikipedia.org/wiki/Lists_of_holidays,

I guess you could find the answer to your detail question there, too.

Chriss

 

[Mike Lewis]

Thanks for the link, Chris.

Reading the Wikipedia list, they clearly have a somewhat liberal definition of the word "holiday". I mean ... April Fool's Day? Guy Fawkes Night? (In fact, it's remarkable that Guy Fawkes Night is commemorated at all, given that it marks a planned terrorist outrage than aimed to assassinate the head of state and kill many innocent bystanders.)

The situation here in Scotland is no less confusing. We have the UK national holidays, such as New Years Day and Easter Monday; Scottish holidays like 2nd January; and local holidays that vary from one city or region to another. And then there are the English bank holidays which are not holidays at all in Scotland but on which banks here are closed at the instigation of the banking unions.

Going back to Presidents Day, I notice that in some states the first word ends in "s" apostrophe, sometimes in apostrophe "s", and sometimes with no apostrophe at all.

I'm not questioning any of this. It's just an example of the fascinating facts that a simple FoxPro question can throw up.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

just an example of the fascinating facts that a simple FoxPro question can throw up

Indeed, Mike.

I bet there are Web APIs that can give you holidays, but not in every regional nuance, and I also understand, if a company or even a single end user decides to maintain their holidays and vacations in some place, it's unnerving to do that in many different places over and over again, just as unnerving as the word unnerving itself, perhaps. No matter how fine routines exist to generate a list of holidays, if you maintain a DBF with such dates it's still only available to the users of that application again.

I also think the Wikipedia page is interesting, what's missing there is a simple list you could use as data and get back to it every year, you can't easily utilize all that information, as detailed as it is, to feed an applications calendar and the mechanism of .hol files also is a standard I never heard before.

AQside of regional and historic reasons for holidays and whether they influence what businesses are open and whether a holiday therefore blocks a day for appointments, there also are school holidays, that could influence that, and they are also not covered by Outlook and another very regionally different set of days.

First results in Goolge may not be best, but

https://openholidaysapi.org/swagger/index.html

for exmple also lists SchoolHolidays as one thing you can retrieve from their API, I didn't look deeper into it, though. But one thought is that if you can deliver one more level of detail, a company might change their standard way to what your own calendar may offer. Not necessary something that makes things easier for yourself, though, if you trade accessing Outlook calendar items against handling a web API.

Chriss

 

[Steve Meyerson]

From the US Office of Personnel Management:

Federal Holidays - 2024 Holiday Schedule
I believe our banks are officially closed each of these days (like today).

Monday, January 01 New Year's Day
Monday, January 15 Birthday of Martin Luther King, Jr
Monday, February 19 Washington's Birthday *
Monday, May 27 Memorial Day
Wednesday, June 19 Juneteenth
Thursday, July 04 Independence Day
Monday, September 02 Labor Day
Monday, October 14 Columbus Day
Monday, November 11 Veterans Day
Thursday, November 28 Thanksgiving Day
Wednesday, December 25 Christmas Day

*This holiday is designated as "Washington's Birthday" in section 6103(a) of title 5 of the United
States Code, which is the law that specifies holidays for Federal employees. Though other
institutions such as state and local governments and private businesses may use other names, it is
our policy to always refer to holidays by the names designated in the law.

HTH
Steve

 

[Mike Lewis]

Thanks for that, Steve. I must admit that I never knew that Columbus Day or Juneteenth were official holidays. There is so much to learn in this forum.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TamarGranor]

Juneteenth is the newest Federal holiday. It was just added after the murder of George Floyd.

On the other side, Columbus Day has been a holiday a long time. There was a major push by Italian-American groups to get it designated back in the days when Italian immigrants were seen as inferior to "real Americans." (Quotes there to indicate that this was racist BS. In those days, I wouldn't have been seen as a "real American," either.) Now there's a lot of pushback against Columbus Day, as we understand that Columbus was a rapist and murderer. Many companies and even some states that celebrate the date have redesignated it as Indigneous Peoples Day, but nothing has happened at the national level yet.

Also, the celebration of Federal holidays is weird. Federal employees (other than essential workers like law enforcement and air traffic controllers) have the day off. Banks are closed. But whether a day is a school holiday varies widely. As a child in Philadelphia in the 60s and 70s, we had off for Columbus Day and Veterans Day, for example, but my kids growing up in a Philly suburb in the 80s and 90s had school on Columbus Day and Veterans Day was a teacher in-service day used for parent-teacher conferences.

Della Martin tells me that in the South, it's common for Memorial Day (which originated as a commemoration by Black Americans after the Civil War) to be the last day of school.

And, of course, a bunch of those are set to always fall on Monday:

MLK Day - 3rd Monday in January
Washington's Birthday/Presidents Day - 3rd Monday in February (which means it never falls on Washington's actual birthday)
Memorial Day - last Monday in May
Labor Day - first Monday in September
Columbus Day - 2nd Monday in October (same as Canadian Thanksgiving)

And Thanksgiving is the 4th Thursday in November.

Tamar