A Formula That Respects Your Time Zone and Your Lunch

There’s a question that comes up more often than you’d expect in our projects: “Is this person, or this system, actually available right now?”

I ran into this again recently while building a feature in Power Apps that needed to behave differently during working hours. The requirement seemed straightforward: only do this thing between 09:00 and 17:00, Monday through Friday, and skip lunch. Done in five minutes, right?

Timezones are hard

The first thing you bump into is that Power Apps, like a lot of platforms, operates in UTC. The app doesn’t care that your users are in Amsterdam or Brussels. It just knows what time it is at Greenwich. So the moment you write TimeValue(Now()), you’re already working with the wrong clock for a Dutch context. The naive fix is to hardcode +1 for CET and call it a day. Something that will work fine until October, when suddenly your “business hours” logic is off by an hour for half the year. So you add a check for daylight saving time (DST) and adjust the offset accordingly.

If you want your formula to be correct year-round, you need to calculate the dates dates dynamically. That means figuring out the last Sunday of a given month, which in Power Fx looks like this:

DateAdd(
    Date(Year(Now()), 3, 31),
    -Mod( Weekday(Date(Year(Now()), 3, 31), StartOfWeek.Monday), 7 ),
    TimeUnit.Days
)

What’s happening here: you take March 31st (the latest a last Sunday can possibly fall), then subtract enough days to land on the most recent Sunday. Same logic applies for October 31st. Once you have both boundary dates, you compare today’s date to know whether you’re in summer or winter time and apply UTC+2 or UTC+1 accordingly.

You can then also exclude the lunch break by checking if the current time falls between 12:15 and 13:00 by using the Not() function:

Not(
    TimeValue(localTime) >= Time(12, 15, 0) &&
    TimeValue(localTime) <  Time(13, 0, 0)
)

Here’s what a proper, daylight savings aware business hours check looks like in Power Apps:

With(
    {
        dstStart: DateAdd(
            Date(Year(Now()), 3, 31),
            -Mod( Weekday(Date(Year(Now()), 3, 31), StartOfWeek.Monday), 7 ),
            TimeUnit.Days
        ),
        dstEnd: DateAdd(
            Date(Year(Now()), 10, 31),
            -Mod( Weekday(Date(Year(Now()), 10, 31), StartOfWeek.Monday), 7 ),
            TimeUnit.Days
        )
    },
    With(
        {
            utcOffset: If(
                DateValue(Now()) >= dstStart && DateValue(Now()) < dstEnd,
                2,
                1
            )
        },
        With(
            {
                localTime: DateAdd(Now(), utcOffset, TimeUnit.Hours)
            },
            Weekday(localTime, StartOfWeek.Monday) in [1, 2, 3, 4, 5]
            && TimeValue(localTime) >= Time(09, 0, 0)
            && TimeValue(localTime) <  Time(17, 0, 0)
            && Not(
                TimeValue(localTime) >= Time(12, 15, 0) &&
                TimeValue(localTime) <  Time(13, 0, 0)
            )
        )
    )
)

What this does, layer by layer:

1. Step 1 Calculate DST boundaries. Last Sunday of March = start of CEST. Last Sunday of October = back to CET. These are recalculated every time based on the current year, so the formula stays correct indefinitely.
2. Step 2 Determine the UTC offset. If today falls between those two Sundays, we’re in summer time (UTC+2). Otherwise, winter (UTC+1).
3. Step 3 Get local Amsterdam time. Apply the offset to Now() to get the actual local time your users experience.
4. Step 4 Apply the business rules. Weekday check (Monday through Friday), time window (09:00–17:00), and a lunch exclusion from 12:15 to 13:00.

The result is a single boolean. true means: yes, we’re open. false means: no, we’re not. Everything else, routing logic, UI states, notifications or rendering text can build on top of that.

Now If your app is only ever used from a single timezone and nobody’s going to maintain it for long, you might not need to go to this level of detail. But if you’re building something that will live in production for a few years, and most enterprise stuff does, then getting this right upfront is significantly cheaper than fixing it after the fact. The formula above is self-contained, self-documenting, and requires zero maintenance.