I collect timestamps for when I arrive at work and when I leave work. The purpose is to calculate working hours automatically.
The timestamp format is like this example:
February 05, 2018 at 08:38AM Left location
February 06, 2018 at 08:19AM Arrived at location
February 08, 2018 at 04:34PM Left location
February 09, 2018 at 07:14AM Arrived at location
February 13, 2018 at 07:18AM Arrived at location
February 13, 2018 at 04:46PM Left location
February 14, 2018 at 09:26AM Left location
February 15, 2018 at 07:23AM Arrived at location
February 16, 2018 at 07:45AM Arrived at location
February 19, 2018 at 07:30AM Arrived at location
February 20, 2018 at 08:57AM Arrived at location
February 21, 2018 at 07:25AM Arrived at location
February 23, 2018 at 08:21AM Arrived at location
February 23, 2018 at 02:22PM Left location
February 25, 2018 at 09:34AM Left location
February 25, 2018 at 12:56PM Left location
February 25, 2018 at 09:33AM Arrived at location
February 25, 2018 at 12:54PM Arrived at location
February 26, 2018 at 04:42PM Left location
Some days, several timestamps occur, as seen Feb 25. To sort out, these rules shall be used:
1. If it is obvious that I left the location and returned at the same day: deduct the absence time from working hours. Mark this day's result in green.
2. If - as Feb 25 - this is not obvious, the earliest arrival and latest departure times shall be calculated with, ignore other timestamps as faulty. Mark this day's result in orange.
3. If one timestamp is missing for one day - either arrive or leave - replace with arrive 07:32 and leave 17:07. Mark this day's result in red.
4. If both timestamps are missing - then this was no working day.
The raw data tab in the google sheet shall be left untouched.
All calculations shall be inserted in a new tab.
If java script / google apps script is used, the code shall be inside the document, commented and pretty. But standard google sheets commands are preferred.
[login to view URL]
8 freelancers are bidding on average €32 for this job
Hello Sir/Madam I have read your task carefully and can do as described. Kindly do message for further discussion. I shall be happy to hear from you. Have a great day Thank you