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?