Date/Time Night Shift Filter Formula

Hello All,

I have and issue with a filter formula, here is the context.

We are working with data in manufacturing that have 2 shifts,

1st shift that works from 6:00 am to 4:00 pm
2nd shift that works from 6:00 pm to 4 :00 am

The thing is that we want to filter to see only the information from the second shift in the 2 tables and the chart, but I dont know how to do that!

If you guys can help me would be very appreciate.



Hello @Victor_Ballesteros !
Trying to do this I realized you cannot use the slider inside the filters to select an end time smaller than the start time so I had to change to formula mode and use the next formula

(thisRow.Fecha.ToTime() >= Time(18,0,0)
or thisRow.Fecha.ToTime() <= Time(4,0,0))

I went a bit further and created a shifts table where the start and end times are stored so, if at any moment these are changed, you won’t have to go to each table to change them.
So the formula ended up something like this

(thisRow.Fecha.ToTime() >= [Turno 2].[hora de Inicio]
or thisRow.Fecha.ToTime() <= [Turno 2].[hora de Fin])

Going even further, the formulas are not the same if the start time is less than the end time, in this case, we would use an AND to evaluate both conditions instead of an OR so we evaluate if the shift meets this condition to use the correct formula

If([Turno 2].[hora de Inicio]>[Turno 2].[hora de Fin], 
  (thisRow.Fecha.ToTime() >= [Turno 2].[hora de Inicio]
  or thisRow.Fecha.ToTime() <= [Turno 2].[hora de Fin]),
  (thisRow.Fecha.ToTime() >= [Turno 2].[hora de Inicio]
  and thisRow.Fecha.ToTime() <= [Turno 2].[hora de Fin])

This way you can change the start and end times to whatever your heart desires and the formulas will still work.

I hope this helps!

1 Like

Hello Saul thank you very much for your help and response!

Just a quick detail maybe I forgot, the filter we need is to show only the rows, that are for today from 6:00 pm to tomorrow 4:00 am, because what we want to see is only the shift data, every day, not the historic one, I don´t know if I explained mayself? Because this will filter all the data that is between those hours, but we need to see the actual shift data filtered on those hours.

Thanks again.