In Progress

calculate working hours in google sheet from IFTTT Data

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:

Raw Data

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.

Google sheet:

[login to view URL]

Skills: Excel, JavaScript

See more: excel calculate work hours days, working hours component joomla, joomla working hours, working hours illustrator, working hours counter, calculate driving distances google maps, working hours sri lanka, log hours contractor sheet, infopath calculate working days, calculate working hours excel, excel calculate working hours start date, excel vba macro calculate working hours, flexi working hours data entry in singapore

About the Employer:
( 3 reviews ) Lund, Sweden

Project ID: #21240446

Awarded to:


I have an experience of 10yrs in ms office, can do your work easily and with 100% accuracy. I am currently working in deccan Iservices Pvt Ltd.

€8 EUR in 2 days
(0 Reviews)

8 freelancers are bidding on average €32 for this job


Hello, I am a full time freelancer and can work dedicated on this till completion. My name is Jay and I'm an Expert Excel and VBA programmer on and have been featured by the freelancer community (goo.g More

€100 EUR in 2 days
(758 Reviews)

Hi, Here is my question of the project: - The arrive and leave office time usually not on same date, so how we collect the working hour on a specific date. Could we find a Arrived at location - Left location pair and s More

€50 EUR in 7 days
(371 Reviews)

Hi, I am ready to start right away. I'm an expert in Excel I have reviewed your job description as well and very confident to handle this project perfectly. I understood your all requirements. I have done many proje More

€30 EUR in 1 day
(17 Reviews)

Hello Sir/Mam, APComp is one of the best providers of software development and other professional IT services. We have over 5 years in the software development field and a long list of satisfied customers. We know tha More

€20 EUR in 4 days
(7 Reviews)

I can complete work with good accuracy. I has a experience in that particular part.i hope you will hire me.

€19 EUR in 3 days
(0 Reviews)

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

€19 EUR in 3 days
(0 Reviews)

I am perfect for your job because i have previous work experience and know the importance of time maintaining and work value.

€9 EUR in 1 day
(0 Reviews)