In N3 I entered this formula:
=ArrayFormula({query(query({A3:D,value(B3:B)}, "select Col1, sum(Col5), count(Col4), sum(Col3) where Col1<>'' group by Col1 format sum(Col5) 'h:mm:ss'"),"select* offset 1",0), vlookup(query(query(query(A4:D, "select D, A, sum(C) group by A, D"), "select max(Col3), Col2 group by Col2 label max(Col3)''"), "select Col1")&unique(sort(filter(A4:A, len(A4:A)))), {query(query(A4:D, "select sum(C), A, D group by A, D"), "select Col1",0)&query(query(A4:D, "select sum(C), A, D group by A, D"), "select Col2",0),query(query(A4:D, "select sum(C), A, D group by A, D"), "select Col3",0)}, 2, 0)})
that seems to output the table you had as the expected output. Maybe someone comes up with something a little shorter (lol) but untill then.. make sure to test it thoroughly...
It's probably easier than you think. You can do arithmetic operations on dates. You may need to tweak formatting, but this should work:
| A | B |
----------------------------
1 | start | end |
2 | 1/3/2017 | 5/31/2017 |
Assuming your start date is in A2, and your end date is in B2...
The difference between your dates can be expressed as =B2-A2.
The difference between your start date and the current date can be expressed as =TODAY()-A2.
Using the division of the two will give you the ratio: =(TODAY()-A2)/(B2-A2). (Don't forget the parentheses, otherwise you'll have an order of operations problem.) Then it's just a question of formatting your output as a percentage. (If you want to shortcut it and not worry about formatting, simply multiply the result by 100.)
Best Answer
Unfortunately it is a bug that has lately appeared in the sheets and has nothing to do with the auto-complete under 'Tools'.
You cannot just "turn it off".
Please use the
Helpmenu to send a report to Google. (the more the people to send, the quicker to be fixed)