# How to isolate the earliest and latest times and eliminate any times in between on base on a date

I'm trying to figure out how to isolate only the first and last times from a specific day in Sheet1 A:A and determine the exact hours between the first "Load Out" and the final "Load In" on Sheet2 column D; however, I need to sum these based on the date range entered in Sheet1 A1 and C1. Our teams load out in the morning and theoretically loads in at the end of the day; however, sometimes they forget something and have to submit another "Load Out" or "Load In". These additional entries are wreaking havoc on the data.

I can achieve what I want using the formula below, but it is clunky:

=QUERY(Sheet1!A:F, "select max(A), min(A) where
A >= date '"&TEXT(\$A\$1,"yyyy-mm-dd")&"' and
A <= date '"&TEXT(\$B\$1,"yyyy-mm-dd")&"' and
F CONTAINS'"&\$A\$3&"'")

Here is a link to the 2 sheet workbook sample. Sample Workbook

• Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
– Community Bot
Commented Jun 24 at 23:50
• Welcome to web apps! We definitely want to help you but your question isn't clear enough. I've answered based on what you included, but please edit with some sample data for sheet1 and sheet2 and an example of your expected result based on that data. If you have a formula you tried, include that too, and any errors or describe what didn't work. Commented Jun 25 at 0:08
• Your example sheet is not shared. Users are not going to request access from you. It should b e read-only and users can make a copy. Commented Jun 26 at 22:21

The integer portion of a datetime is the date, and the decimal portion is the time. Knowing this you can filter the datetimes using the INT function to compare only dates.

#### MAX - MIN Datetimes by Date

Replace myDate in the formula by a reference to a cell containing your lookup date

=LET(dt, FILTER(A:A, INT(A:A)=myDate), (MAX(dt)-MIN(dt))*24)

Slighly shorter by moving *24 into FILTER

=LET(dt, FILTER(A:A*24, INT(A:A)=myDate), MAX(dt)-MIN(dt))
1. FILTER returns the datetimes from A:A that are on myDate using INT(A:A)=myDate and LET stores the array of datetimes (converted to hours by *24) in dt
2. The difference between the maximum dt and the minimum dt is returned using MAX(dt)-MIN(dt)
• I was able to use this formula to get the total hours between the min/max times on a specific day, but when I try to put conditions on the results, I get errors. I've tried IF, FILTER, QUERY with this formula. There are two conditions that I need to add. Commented Jul 1 at 12:40
• @JayBryant What conditions? I can't tweak it without access to your data/desired results. See my comments on your question. At a minimum change shared spreadsheet to anyone can view. view only to protect other users' privacy, or include data in question or share anonymously using Blank Sheet Maker Commented Jul 1 at 20:23