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 formulas (gave up with VBA since it causes too much lag when trying to automate everything) and a missing formula for a column. This is about the missing formula. I am trying to get a dynamic formula that represents the amount of overlap of people with respect to their shifts. The complexity is the presence of the night shift that usually passes midnight (00:00). I work with a 24-hour schedule. I mainly get problems with night shifts that result in incorrect values. Attached is a shortened Excel file version of the table with the two (most important) trial formulas that failed (Dutch and English). I would like to hear if there is any advice on this. 

Kind regards,

Bo.

excel
Formulas and Functions
office 365

1 Reply

  • 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