Forum Discussion

Bo_Krijnen's avatar
Bo_Krijnen
Copper Contributor
Feb 01, 2025

Shift overlap calculation:

https://1drv.ms/x/c/c1d91c448005a7ba/EaQX5492IcdLsJl9InHCvJ8BuStKD0xIIdjQaR2fqH9l3w?e=y4dlFO Hello, I am working on a shift planner and it is almost finished. The only problem is optimising the f...
excel
Formulas and Functions
office 365
Patrick2788's avatar
Patrick2788
Silver Contributor
Feb 03, 2025

Since the goal is a Gantt chart, there's no need to get too complex with the formula. All we need is to determine the duration of shifts and deal with the exceptions going from PM to AM in the same shift.

=IF(
    [@[End time:]] < [@[Start time:]],
    (1 + [@[End time:]]) - [@[Start time:]],
    [@[End time:]] - [@[Start time:]]
)

With the duration obtained, it becomes a matter of creating a Gantt Chart (a modified stacked column chart):

 

Resources