0

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?

Improve this question
2

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").

Improve this answer
3
  • Wow, thanks a lot for that, much appreciated! Last question (please tell me if I should create a new question for this): How could I summarize all the minutes per day so that I can analyze how much hours I worked for the last days/weeks? – Peleke Nov 6 '18 at 8:24
  • I'm not sure I understand exactly what you are trying to do. What do you mean by "summarize"? Summarize by what frequency: every day? weekly? monthly? all of these? – Erik Tyler Nov 8 '18 at 18:30
  • I have created another question here: webapps.stackexchange.com/questions/122012/… – Peleke Nov 9 '18 at 8:39
1

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.

Improve this answer
1
1

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.

Improve this answer
2
  • I tried to achieve this with a script but that doesn't work: var range = ss.getRange("B:B").getValues(); // this is a loop, to go through multiple cells that may contain the text, that needs to be replaced. for (var i = 0 ; i<range.length ; i++) { var le = range.length; var stri = range[i].toString().replace(".", ":"); } – Peleke Nov 5 '18 at 9:11
  • Thanks, but that would be overwritten as soon as the imported csv is getting updated and it is always a manual process. – Peleke Nov 6 '18 at 8:25

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.