Filtering calendar list view to show events that take place during today

Question

I feel like I'm missing something here.

We have a calendar that communicates staff whereabouts (on leave, working from home, etc). The calendar has a list view that needs to display any events that take place either on (ie. 4 hour event during that day, all day event during that day) or during that particular day (ie. if it's the 25th today and the event is 24th-26th, then it should also display).

The filter for Today view is set to:

• Start Time is less than or equal to [Today] AND
• End Time is greater than or equal to [Today]

However, using this filter, an event for today that goes for 4 hours for example, is not displayed when it should be.

An all-day event for the previous day is displayed, when it should not be.

All-day events for the current day or events which start before and end after current day are displayed, which is correct behaviour.

I have a hunch this has something to do with either time zone or the work week definition in Regional Settings, but I'm not sure how the work week influences how filters operate. Or I could be missing a step in my filter logic.

Answer 1

Managed to work out a solution finally. I don't understand it completely but there appears to be an issue where using [Today] uses GMT0 as opposed to GMT+10 (local timezone), also appears that some formula's also use GMT0 and ignore the timezone set in Regional Settings.

I was able to circumvent the issue by adding a calculated column that subtracts 11 hours from the End Time using the formula

=[End Time]-(1/24/60*660)

Where 1 day is divided by 24 hours, divided by 60 minutes, then multiplied by the number of minutes needed to make 11 hours (660).

Appears to be some undocumented behaviour that affects how formulas, views and filters interact with date/time values and the user time zone.