Search first entry and last entry based on time range and date

[haskellf]

I have a google sheet with data that contains users logging into an account based on time and date.

The sheet looks like this:

CreateDate | Location | Date | Time

I am looking to create a virtual column called "StartTime" which will search the "Time" column for the very first entry between the hours of 7am -9am for each day .

Likewise, I want to create a virtual column called "EndTime" which will search the "Time" column for the very last entry between the hours of 2pm-4pm for each day.

The end result will be a list of start and end times for each day.

[Marc_Dillon]

https://help.appsheet.com/en/articles/2357297-min

https://help.appsheet.com/en/articles/2357296-max

https://help.appsheet.com/en/articles/2357314-select

[Suvrutt_Gurjar]

Typically such multirow expressions to find minimum or maximum values acoss a large number of rows are sync time expensive. You could evaluate approaches such as security filter, if feasible in your case.

You may also create slices for Start time and end time ranges.

For example for Start time range, the slice expression could be 

AND([Start Time]>="07:00:00", [Start Time]<="09:00:00")

Then the minimum start time each day could be 

MIN(SELECT(StartTimeRange[Start Time], ,[Date]=[_THISROW].[Date]))

Where "StartTimeRange" is the slice name with the expression described above. You may also need to add the email/name of user in the VC expression to find the times by each user.

https://help.appsheet.com/en/articles/2357297-min

https://help.appsheet.com/en/articles/2357296-max

You can similarly use MAX() expressions for finding the end time.

[haskellf]

Thanks, this works great!

Is there a way I can combine both the min and max expressions within a single expression? I tried using:

AND(AND([Start Time]>="07:00:00", [Start Time]<="09:00:00"), MIN(SELECT(StartTimeRange[Start Time], ,[Date]=[_THISROW].[Date])))

but AppSheet complained about requiring Yes/No.

Any ideas?

[Suvrutt_Gurjar]

@Marc_Dillon 

Sorry, realized you had already responded, after I posted the response. Somehow , if the post page is open, it does not update if someone has responded in the interim.

[Suvrutt_Gurjar]

May we know where are you using this expression and what are you trying to achieve? Also you do not seem to be merging MIN() and MAX(). The expression has only MIN()

[haskellf]

Specifically, I have a Ring camera in front of my jobsite gate. I am using IFTTT automation to create a new google sheet row each time the gate camera sees a motion. 99% of the motions are from the contractor. Therefore, the first motion entry of the day will be the contractor arriving onsite and the last motion of the day range from 2:30am-3:30pm is the contractor leaving the job site. Every other motion alerts are not needed.

GOAL: I want to extract that start time and the end time for the contractor in order to determine the hours worked onsite for each day. 

The data I receive from IFTTT is as follows:

Event | CreateDate | Location

Example data:

Motion Event | February 19, 2022 at 08:03AM | Front Gate

To extract the Time, I create a VC column with the following expression:

Time(EXTRACTTIMES([CreateDate]))

To extract the Date, I create a VC column with the following expression:

Date(EXTRACTDATES([CreateDate]))

[Suvrutt_Gurjar]

Thank you for all the details. But could you respond where you wish to use the expression you have shared in the previous post.

AND(AND([Start Time]>="07:00:00", [Start Time]<="09:00:00"), MIN(SELECT(StartTimeRange[Start Time], ,[Date]=[_THISROW].[Date])))

[haskellf]

I am looking to use this expression in a new VC column to determine the Start Time. A second VC will determine the End Time based on another range using Max. Finally, I will then minus Start Time from End time to get the duration of hours that they worked.

[Suvrutt_Gurjar]

In the suggested approach the VC with the following expression will give the minimum start time

"Then the minimum start time each day could be 

MIN(SELECT(StartTimeRange[Start Time], ,[Date]=[_THISROW].[Date]))"