1

I have been on this a couple of days and I cannot get my head around this ... I use a spreadsheet to track the number of miles traveled by drivers and I want to work out how many miles a driver has traveled between 2 specified dates. Here is the spreadsheet.

enter image description here

Now using this formula I can easily find out the milages I start with.

= MIN(FILTER('Sheet1'!C2:C, 'Sheet1'!A2:A >= START_DATE, 'Sheet1'!A2:A <= END_DATE))

However, if I use a similar formula for the milages traveled up to the END_DATE I do not get the piece of information I need.

= MAX(FILTER('Sheet1'!C2:C, 'Sheet1'!A2:A >= START_DATE, 'Sheet1'!A2:A <= END_DATE))

So let's say I want to find out how many miles the driver traveled between June 18th and June 24 the first formula return the correct value

1690

However, the second one returns the value

1772

without taking account the miles traveled the late shift. Is there a way to get that entry?

Many thanks for all your inputs!

0

=ARRAYFORMULA(MAX(QUERY({IF(ROW(Sheet1!A2:A) <= MAX(IF(NOT(ISBLANK(Sheet1!B2:B)), 
 ROW(Sheet1!A2:A))), VLOOKUP(ROW(Sheet1!A2:A), FILTER({ROW(Sheet1!A2:A), Sheet1!A2:A},
 LEN(Sheet1!A2:A)), 2), ), Sheet1!C2:C}, 
 "select Col2 
  where Col1 >= "&DATEVALUE(F1)&" 
    and Col1 <= "&DATEVALUE(F2), 0)))

0

  • wow, that was quick! and that is only to work out the max value of the END_DATE, correct? – Danny Jul 28 at 18:54
  • @Danny you can use it for MIN too if you change MAX to MIN like: =ARRAYFORMULA(MIN(QUERY({IF(ROW(Sheet1!A2:A) <= MAX(IF(NOT(ISBLANK(Sheet1!B2:B)), ROW(Sheet1!A2:A))), VLOOKUP(ROW(Sheet1!A2:A), FILTER({ROW(Sheet1!A2:A), Sheet1!A2:A}, LEN(Sheet1!A2:A)), 2), ), Sheet1!C2:C}, "select Col2 where Col1 >= "&DATEVALUE(F1)&" and Col1 <= "&DATEVALUE(F2), 0))) – user0 Jul 28 at 18:57
  • awesome! but I am having some issue with my date as on the reporting sheet it is display as "dd/mm". is there a conversion I can apply before handing it to the DATEVALUE function? – Danny Jul 28 at 19:03
  • 1
    it was super helpful! just needed a tiny tweak due to the formatting of the date, thank you ever so much! – Danny Jul 28 at 19:51
  • 1
    much appreciated! – Danny Jul 28 at 21:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.