← Ramblings

The Moon Doesn't Care About Your Date Table

Calendar days measure time. Working days measure effort. Here is how to build a master calendar that respects both, in a country where the holidays follow the moon.

Every date-table tutorial you have ever read is quietly lying to you. Not on purpose. It just assumes you live somewhere where holidays sit still.

New Year is 1 January. American Thanksgiving is the fourth Thursday of November. You can write those as a formula and never think about them again. Most Power BI and Excel calendar templates do exactly that: a column of dates, a couple of IF statements, done. Then you try it in the UAE, and the whole thing quietly falls apart. Before we get to why, we need to be honest about a distinction most reports blur.

Two kinds of days

There are two ways to count days, and they answer two completely different questions.

Calendar days measure time. They are the clock the customer feels. How old is this case. How long has this invoice been sitting. When, on a wall calendar, is this due. Calendar days do not care whether anyone was at their desk; they just tick.

Working days measure effort. They are the runway the team actually has. Five working days is five days someone could have moved the thing forward. Weekends and public holidays are not effort. Nobody chased that approval on Eid.

A deadline is a calendar date. The work you can fit before it is measured in working days. Confuse the two and you will either over-promise or unfairly punish.

Here is the same ten-day task, counted both ways:

Why SLAs live or die on this

An SLA is usually written in working days: "complete within 10 working days." That is a promise about effort. But the thing everyone actually looks at, the breach flag, the red cell on the dashboard, is a calendar date: the deadline. Getting from one to the other is where teams quietly bleed credibility.

Do it naively, count 10 calendar days from the start, and you punish people for weekends and holidays they never worked. Do it right, find the calendar date on which the 10th working day falls, and the deadline is honest.

Take a real example. A procurement task with a 10 working-day SLA, issued Monday 24 March 2025, right before Eid al-Fitr:

Same SLA, same start date, six calendar days apart, purely because of how you count. Multiply that across a few hundred cases and you understand why "why is this red, we were on holiday" is the most tiring email in operations. The fix is not a smarter dashboard. It is a calendar that knows the difference before anyone runs a report.

The lunar wrinkle

Now the part that breaks the formula. In the UAE the big public holidays, Eid al-Fitr, Eid al-Adha, Islamic New Year, the Prophet's Birthday, follow the lunar calendar. The exact day is confirmed by moon sighting, sometimes only a day or two ahead. There is no =EID(year). A day you had pencilled in as a normal Tuesday becomes a public holiday because a committee looked at the sky.

So a calendar built on the assumption that holidays are computable is wrong, in the UAE, about a dozen days a year, every year, and they are exactly the days your SLA maths cares about most.

The one idea to take away

Split your world into two piles.

Pile one: what you can compute. The Gregorian skeleton. Year, month, quarter, weekday, whether a day is a weekend. This never changes and never surprises you.

Pile two: what you have to be told. The lunar holidays. You cannot derive these. You fetch them from someone who already knows, and you re-fetch, because next year's dates do not exist yet.

Every regional-calendar mistake I have seen comes from forcing pile two into pile one. Stop. They are different kinds of knowledge. Build them separately, then join them.

The shape of the thing

Three small scripts, each doing one job, run in order:

• get_holidays_and_rates, goes to the web, scrapes the confirmed UAE public holidays [and, since I was there anyway, the AED exchange rates], and drops them into a tiny local SQLite file.
• do_master_calendar, generates the Gregorian spine, joins the holidays onto it, and works out which days actually count as working days.
• run_all, the orchestrator. It calls the other two in the right order so I never have to remember which comes first.

That order is not decoration. The calendar depends on the holidays existing first. Fetch, then build. Build before you fetch and you are just merging against yesterday's truth.

Part one: get the holidays, do not guess them

The temptation is to hardcode. Paste this year's Eid dates into a list and move on. Do that and you have signed up to edit code every year, forever, and to be wrong the year you forget.

Instead, treat the holidays as data you go and collect. I scrape a public holidays site, one page per year, with requests to pull the HTML and BeautifulSoup to read it, and I keep only the national holidays:

# each row is one holiday; the "comments" cell tells us its type
holiday_type = row.find("td", class_="comments").text.strip()

if holiday_type == "National Holiday":
    holidays.append({
        "Date": date,
        "Holiday_Name": holiday_name,
        "Type": holiday_type,
        "Updated_On": timeStampMe,
    })

Two deliberate choices in there.

First, I filter to National Holiday on purpose. Observances and "sort of a holiday" days are noise for a working-day calculation. If the office is open, it is a working day, and I do not care that it is technically Flag Day.

Second, everything lands in a local _helpers.db SQLite file, not in the script. The script is logic. The holidays are facts. Keeping facts out of your code is one of those habits that pays you back quietly for years.

Part two: build the spine and count what matters

Now the calendar itself. Start with a fixed epoch. Mine is 1 January 2023, because that is where my reporting actually begins. One line of pandas gives me every day from then to a year into the future:

# one row per calendar day, start of 2023 to a year out
calendar_df = pd.DataFrame({
    "Date": pd.date_range(start=start_date, end=end_date, freq="D")
})

Then the cheap, computable columns: year, month, quarter, weekday name. And the one that matters, the weekend flag. The UAE working week is Monday to Friday, so a weekday index below five is a workday [Monday is 0]:

# Monday=0 ... Sunday=6, so weekday < 5 means Mon–Fri
calendar_df["WorkDay"] = calendar_df["Date"].dt.weekday < 5

Here is where the two piles meet. I join the scraped holidays onto the spine with a left merge, and anything that matched becomes a holiday:

# left join keeps every calendar day; matched rows get Holiday=True
calendar_df = calendar_df.merge(
    uae_holidays.assign(Holiday=True), on="Date", how="left"
)
calendar_df["Holiday"] = calendar_df["Holiday"].notna()

# the actual point of all this: a working day is a weekday the moon did not steal
calendar_df["WorkingDay"] = calendar_df["WorkDay"] & (~calendar_df["Holiday"])

That single boolean, WorkingDay, is what your SLA and ageing logic should be built on. Not "weekday." Not "not weekend." A weekday that the moon did not steal.

The payoff: a number you can do maths with

One more column, and it is my favourite. A running count of working days since the epoch:

# cumulative sum of a boolean counts the True values as we go down the calendar
calendar_df["WDNumber"] = calendar_df["WorkingDay"].cumsum()

WDNumber is a monotonic ledger of working days. The epoch is the anchor. Every working day after it ticks up by one; weekends and lunar holidays do not move the counter. It looks like this:

Five days pass on the wall calendar and the ledger barely moves, because almost none of them were effort. That is the whole point made visible.

Why care so much about one integer? Because it turns "how many working days between these two dates" into subtraction. No looping over a calendar at query time. No re-checking holidays for every case. Look up the WDNumber at the start, the WDNumber at the end, subtract, and you have a genuine working-day duration that already understands Eid without being told again.

And the SLA deadline from earlier becomes trivial. The task started on a day with some WDNumber = N. A 10 working-day SLA is simply the first date where WDNumber = N + 10. That row's Date is your honest, holiday-aware deadline, the 9 April, not the 3 April. Every ageing bucket, every breach flag, every "days in stage" measure downstream just reads this column and trusts it.

Do the hard, honest work once, in the calendar, so everything downstream gets to be lazy and correct.

A small lesson in not trusting your database

One habit worth stealing. When I read from SQLite I never call pd.read_sql raw. I wrap it, so a missing table returns None instead of exploding the whole run:

def read_sql_safe(query, db_file, parse_dates=None):
    try:
        conn = sql3.connect(db_file)
        df = pd.read_sql(query, conn, parse_dates=parse_dates)
        conn.close()
        return df
    except (sql3.Error, pd.errors.DatabaseError) as e:
        print(f"ERROR reading SQL: {e}")
        return None

Then the caller checks for None and stops cleanly. A calendar built on a half-loaded holidays table is worse than no calendar, because it looks right. Fail loud, fail early.

Things I want you to get right, because I got them wrong first

• Scraping is a handshake, not a guarantee. The day that website changes its HTML, your holidays stop arriving. Filtering on a specific table class is fragile by nature, so log what you fetched and never let a silent empty result poison the calendar.
• Confirmed is not final. Lunar dates can shift by a day even after they are published, because the sighting is the sighting. Re-run the fetch on a schedule and treat a far-off Eid as a best estimate.
• Cross-check the official source. A public holidays site is convenient, not authoritative. For anything touching real obligations, reconcile against the UAE government listing before you trust it.
• Pick your epoch on purpose. Mine is 2023-01-01 because that is where my data starts. Choose yours to match the earliest date your reports genuinely need, then never move it. Everything anchors to it.

The whole idea, one more time

A calendar is not a lookup table. It is a small model of how your organisation actually experiences time. In a lunar country, part of that experience is genuinely unknowable in advance, and the grown-up response is not to pretend otherwise. It is to build a spine you can compute, fetch the part you cannot, join them cleanly, and hand everything downstream a single number it can trust.

The moon is going to keep doing its thing. Your calendar might as well be honest about it.

Full source

The load-bearing lines are above. If you want the whole thing to read end to end, here it is.

import pandas as pd
from datetime import datetime, timedelta
import pytz
from pathlib import Path, WindowsPath
import sys
from utils.db import insert_dataframe_to_db, read_sql_safe


def main():
    # --- CONFIG ---
    myDBDir = WindowsPath(r"C:\Users\juan.chang\...\00.Helpers")
    myDBFile = "_helpers.db"
    helpersHolidaysDBTable = "uae_holidays"
    DBFile = Path(myDBDir, myDBFile)
    DB_Calendar_Table = "master_calendar"

    # current Dubai time
    dubai_time = datetime.now(pytz.utc).astimezone(pytz.timezone("Asia/Dubai"))
    today = dubai_time.date()
    timeStampMe = dubai_time.strftime("%Y-%m-%d %H:%M:%S")

    # supporting table first
    uae_holidays = read_sql_safe(
        f"SELECT Date FROM {helpersHolidaysDBTable}", DBFile, parse_dates=["Date"]
    )
    if uae_holidays is None:
        sys.exit(1)

    # date range and calendar
    start_date = datetime(2023, 1, 1).date()
    end_date = today + timedelta(days=365)
    calendar_df = pd.DataFrame({"Date": pd.date_range(start_date, end_date, freq="D")})

    # breakdown
    calendar_df["Year"] = calendar_df["Date"].dt.year
    calendar_df["Month"] = calendar_df["Date"].dt.month
    calendar_df["Day"] = calendar_df["Date"].dt.day
    calendar_df["Quarter"] = "Q" + calendar_df["Date"].dt.quarter.astype(str)
    calendar_df["Month_Name"] = calendar_df["Date"].dt.strftime("%b")
    calendar_df["Weekday"] = calendar_df["Date"].dt.strftime("%a")
    calendar_df["WorkDay"] = calendar_df["Date"].dt.weekday < 5   # Mon–Fri

    # holidays
    calendar_df = calendar_df.merge(
        uae_holidays.assign(Holiday=True), on="Date", how="left"
    )
    calendar_df["Holiday"] = calendar_df["Holiday"].notna()

    # working day = weekday and not holiday
    calendar_df["WorkingDay"] = calendar_df["WorkDay"] & (~calendar_df["Holiday"])

    # running working-day number [the ledger]
    calendar_df["WDNumber"] = calendar_df["WorkingDay"].cumsum()

    calendar_df["updated_on"] = pd.to_datetime(timeStampMe)

    insert_dataframe_to_db(calendar_df, DB_Calendar_Table, DBFile, do_if_exists="replace")
    print("Master calendar created successfully.")


if __name__ == "__main__":
    main()

import pandas as pd
import pytz, requests, urllib3, re
from bs4 import BeautifulSoup as bs
from datetime import datetime, timedelta
from utils.helpers import deco_log_and_time
from utils.db import insert_dataframe_to_db
from pathlib import Path, WindowsPath
from typing import Optional

# --- CONFIG ---
DBFile = Path(WindowsPath(r"C:\Users\juan.chang\...\00.Helpers"), "_helpers.db")
DB_Holidays_Table = "uae_holidays"

dubai_time = datetime.now(pytz.utc).astimezone(pytz.timezone("Asia/Dubai"))
timeStampMe = dubai_time.strftime("%Y-%m-%d %H:%M:%S")
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)

UAE_Calendar_URLs_sources = [
    ("https://www.officeholidays.com/countries/uae/2023", "2023"),
    ("https://www.officeholidays.com/countries/uae/2024", "2024"),
    ("https://www.officeholidays.com/countries/uae/2025", "2025"),
    ("https://www.officeholidays.com/countries/uae/2026", "2026"),
]

@deco_log_and_time
def scrape_UAECalendar_data(url: str) -> Optional[pd.DataFrame]:
    try:
        response = requests.get(url, verify=False)
        if response.status_code == 200:
            myBS = bs(response.text, "html.parser")
            holidays_table = myBS.find("tbody")
            if holidays_table:
                rows = holidays_table.find_all("tr", class_=re.compile(r"country(-past)?"))
                holidays = []
                for row in rows:
                    day = row.find("td").text.strip()
                    date = row.find("time", itemprop="startDate").get("datetime")
                    name = row.find("a", class_="country-listing").text.strip()
                    htype = row.find("td", class_="comments").text.strip()
                    if htype == "National Holiday":
                        holidays.append({
                            "Day": day, "Date": f"{date}", "Holiday_Name": name,
                            "Type": htype, "Updated_On": timeStampMe,
                        })
                return pd.DataFrame(holidays)
    except Exception as e:
        print(f"<=== Error: {e}.")


def do_me_UAECalendars(sources) -> pd.DataFrame:
    dfs = []
    for url, year in sources:
        print(f"====> {year}")
        dfs.append(scrape_UAECalendar_data(url))
    return pd.concat(dfs, ignore_index=True)


def main():
    myDF = do_me_UAECalendars(UAE_Calendar_URLs_sources)
    insert_dataframe_to_db(myDF, DB_Holidays_Table, DBFile, do_if_exists="replace")


if __name__ == "__main__":
    main()

import sqlite3 as sql3
import pandas as pd
from typing import Optional, List


def create_connection(db_file: str) -> sql3.Connection:
    try:
        return sql3.connect(db_file)
    except sql3.Error as e:
        print(f"[ERROR] Cannot connect to {db_file} -> {e}")
        return None


def insert_dataframe_to_db(df, table_name, db_file, do_if_exists="replace") -> None:
    conn = create_connection(db_file)
    if conn:
        try:
            df.to_sql(table_name, conn, if_exists=do_if_exists, index=False)
            conn.commit()
            print(f"Inserted {len(df)} rows into {table_name}")
        except sql3.Error as e:
            print(f"[ERROR] Failed to insert into {table_name} -> {e}")
        finally:
            conn.close()


def read_sql_safe(query, db_file, parse_dates: Optional[List] = None):
    try:
        conn = sql3.connect(db_file)
        df = pd.read_sql(query, conn, parse_dates=parse_dates)
        conn.close()
        return df
    except (sql3.Error, pd.errors.DatabaseError) as e:
        print(f"ERROR reading SQL: {e}")
        return None

from datetime import datetime
import pytz


def main():
    print("=== Running get_holidays_and_rates ===")
    import get_holidays_and_rates
    get_holidays_and_rates.main()

    print("=== Running do_master_calendar ===")
    import do_master_calendar
    do_master_calendar.main()

    stamp = datetime.now(pytz.timezone("Asia/Dubai")).strftime("%Y-%m-%d %H:%M:%S")
    print(f"=== All jobs completed on {stamp} ===")


if __name__ == "__main__":
    main()

← Back to all ramblings