I collect timestamps for when I arrive at work and when I leave work. The purpose is to calculate working hours automatically. The calculation shall not only be done with the sample data. New data is coming in daily and the calculation shall be done completely automatically.
Formatting with colors shall not be done manually, but by either formulas, java script or by defining conditional formatting rules in Google Sheets.
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. 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. Start with copying the raw data to the calculation sheet.
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]
11 pekerja bebas membida secara purata €30 untuk pekerjaan ini
Hello I can make a python script that will process your data in real time and post results and Gsheets. All of this will be done through python automated scripts, no manual processing