How to analyze a time tracking sheet with Google Sheets?

Question

I have a simple time tracking set up with Tasker on my phone that creates a new line every time I connect to my work Wifi. I upload this csv file regularly to Google Drive and import it to a Google Sheet with a Script. In this script I have two tabs, one with the imported raw data (which is overwritten once per day) and one with the analysis.

A (Date)    B (Time) C (Arrived or left work/Wifi)
10-15-18    18.55    Start
10-15-18    18.59    Stop
10-16-18    08.57    Start
10-16-18    09.02    Stop

Now I want to analyze the amount of hours per day (a sum of the time in column B, duration between Start and Stop). One problem I have is with the dot in column B for the time which is automatically created by Tasker in the csv file. In the analysis tab I have this formula:

=ArrayFormula(IF(Rohdaten!C2:C="Stop";TO_PURE_NUMBER(Rohdaten!B2:B) - TO_PURE_NUMBER(Rohdaten!B1:B);))

This should calculate the duration but throws an error because it is not able to detect/convert the text to a number. I want to use a similar solution as proposed here. How can I fix it?

Answer 1

Peleke, I just looked at your linked sheet. Some of your data in Rohdaten!A:A is dates (those that are right-aligned) and some are text that just looks like dates (those that are left-aligned). Similarly, some of your data in B:B is numbers (right-aligned) and some is text (left-aligned). How you got mixed data in the same column, I don't know. But, no, my formula assumed your dates were all real dates and your times were all real numbers, not a mix of some dates, some numbers and some text.

I chose to spend some more time with your sheet. It was not easy, but I wrestled a formula together that will account for your mix of text and numbers. It's placed in your first sheet, cell A2 (not A1, since your first "Stop" is in C2 of the next sheet).It is working now.

The new formula:

=ArrayFormula(IF(Rohdaten!C2:C="Stop";(TIME(LEFT(TO_TEXT(Rohdaten!B2:B);FIND(".";TO_TEXT(Rohdaten!B2:B))-1);MID(TO_TEXT(Rohdaten!B2:B);FIND(".";TO_TEXT(Rohdaten!B2:B))+1;2);0)-TIME(LEFT(TO_TEXT(Rohdaten!B1:B);FIND(".";TO_TEXT(Rohdaten!B1:B))-1);MID(TO_TEXT(Rohdaten!B1:B);FIND(".";TO_TEXT(Rohdaten!B1:B))+1;2);0))*60*24;""))

This is not ideal, to have mixed data coming into your sheet. But if that is all you have, then this formula should keep up with it.

I also formatted the column that contains this formula to show the number of minutes followed by "min" (e.g., "160 min").

Answer 2

Peleke, try replacing your formula with this:

=ArrayFormula(IF(Rohdaten!C2:C="Stop";(TIME(INT(Rohdaten!B2:B);(Rohdaten!B2:B-INT(Rohdaten!B2:B))*100;0)-TIME(INT(Rohdaten!B1:B);(Rohdaten!B1:B-INT(Rohdaten!B1:B))*100;0))*60*24;""))

The TIME function turns (h, m, s) into a viable time. I've used INT to grab the first part of your time (the hour) and the raw number minus that INT value to get the decimal portion (the minute). Then the minute, which is still in decimal form, is multiplied by 100 to get the actual number of minutes.

For instance, if the raw value in B2 were 18.59:

INT(B2) returns 18

B2-INT(B2) returns .59

But the TIME function needs an integer for all values (i.e., hour 18, minute 59, second 0). So multiplying the decimal part by 100 provides us with the 59 we need.

From there, the formula works similarly to what you were trying to do. But the resulting subtraction of times will be a decimal value, where I assume you want a whole number of minutes. Because of this we multiply by the number of minutes in an hour and the number of hours in a day at the end of the formula (i.e., *60*24). This provides you with the number of minutes in each duration.

Answer 3

Try pusing formula to replace . with :

1. Insert a column next to your time entries.
2. Use substitute function to replace "." with ":" a. In cell D2 write "=SUBSTITUTE(B2)" b. Drag or double tap on right bottom corner c. Copy D:D d. Paste as value in B:B
3. Now, apply your function as before.

Hope this helps.