Filtering Datetime In Crossover Over Date

[desmond_lee]

Hi Guys

How do I filter the current active (today shift) shift datetime? If current shift is 7/2/2025 7pm until 7/3/2025 7am how do i filter those rows from 7/2/2025 until 7/3/2025? I am referring to live current datetime it mean tomorrow will be another datetime and so on....

[Steve]

Like this?

AND(
  ([CLOCK IN] >= (DATETIME(start-date) + shift-start-time)),
  ([CLOCK IN] < (DATETIME(start-date) + shift-start-time + shift-duration))
)

For today:

AND(
  ([CLOCK IN] >= (DATETIME(TODAY()) + "19:00:00")),
  ([CLOCK IN] < (DATETIME(TODAY()) + "19:00:00" + "012:00:00"))
)

[desmond_lee]

Thank very much Steve

May I ask what is this mean ([CLOCK IN] < (DATETIME(TODAY()) + "19:00:00" + "012:00:00") ? first time I see this part + "19:00:00" + "012:00:00

[Minhku]

DATETIME(start-date)

returns Today 00:00:00 (24 hours format) or Today 12:00:00 am (12 hours format)

(DATETIME(TODAY()) + "19:00:00")

returns Today at 19:00:00 (24 hours format), its the start time of the shift

DATETIME(TODAY()) + "19:00:00" + "012:00:00"

Means the start time of the shift + 12 hours (duration). It is equivalent to the end time of the shift on the day after (worked overnight)

So, Steve's formula is to find all the records within this time frame.

I hope this helps 🙂

[desmond_lee]

Wow thank very much. I learn something new today. In appsheet I hate the date function. I did it in another way. But i think your is more practical. AND(
ISNOTBLANK([SHIFT START TIME]),
ISNOTBLANK([SHIFT END TIME]),
[CLOCK OUT] > [CLOCK IN],
TOTALHOURS([CLOCK OUT] - [SHIFT START TIME]) <= 15)

[desmond_lee]

This display or filter windows is Shift Code = 11 is 5am to 23pm (On the same day) and shift code =12 5pm until the next day 10am. The strange thing is it's already 11am the next day the shift code =12 still shows up (not filtering). Wonder where is my mistake in this expression

OR(
IF(
AND(
[SHIFT CODE] = 12,
[CLOCK IN] > DATETIME(TEXT(TODAY(), "YYYY-MM-DD") & " " & TEXT("17:00:00", "HH:MM:SS")),
[CLOCK IN] < DATETIME(TEXT(TODAY(), "YYYY-MM-DD") & " " & TEXT("23:59:59", "HH:MM:SS"))),
AND(
[SHIFT CODE] = 12,
[CLOCK IN] > DATETIME(TEXT(TODAY(), "YYYY-MM-DD") & " " & TEXT("17:00:00", "HH:MM:SS")),
[CLOCK IN] <= DATETIME(TEXT(TODAY(), "YYYY-MM-DD") & " " & TEXT("23:59:59", "HH:MM:SS"))),""),
OR(
IF(
AND(
[SHIFT CODE] = 12,
[CLOCK IN] < DATETIME(TEXT(TODAY()-1, "YYYY-MM-DD") & " " & TEXT("23:59:59", "HH:MM:SS"))),
AND(
[SHIFT CODE] = 12,
[CLOCK IN] > DATETIME(TEXT(TODAY()-1, "YYYY-MM-DD") & " " & TEXT("17:00:00", "HH:MM:SS")),
[CLOCK IN] < DATETIME(TEXT(TODAY()-1, "YYYY-MM-DD") & " " & TEXT("23:59:59", "HH:MM:SS"))),""),

IF(
AND(
[SHIFT CODE] = 12,
[CLOCK IN] >= DATETIME(TEXT(TODAY(), "YYYY-MM-DD") & " " & TEXT("00:00:00", "HH:MM:SS"))),
AND(
[SHIFT CODE] = 12,
[CLOCK IN] > DATETIME(TEXT(TODAY(), "YYYY-MM-DD") & " " & TEXT("00:00:00", "HH:MM:SS")),
[CLOCK IN] < DATETIME(TEXT(TODAY(), "YYYY-MM-DD") & " " & TEXT("10:00:00", "HH:MM:SS"))),""))
)

This is shift code =11. I will use OR() to combine both as one expression

AND(
[SHIFT CODE] = 11,
[CLOCK IN] >= DATETIME(TEXT(TODAY(), "YYYY-MM-DD") & " " & TEXT("05:00:00", "HH:MM:SS")),
[CLOCK IN] <= DATETIME(TEXT(TODAY(), "YYYY-MM-DD") & " " & TEXT("23:00:00", "HH:MM:SS"))
)