Sedang Disiapkan

calculate working hours in google sheet from IFTTT Data -- 2

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:

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

Google sheet:

[login to view URL]

Kemahiran: Excel, Javascript

Lihat lagi: ifttt gmail to sheets, ifttt google sheets trigger, ifttt google sheets usage limit, ifttt there was a problem with the google sheets service, ifttt google sheets failed to validate, ifttt google sheets ingredients, ifttt google forms, ifttt google sheets not working

Tentang Majikan:
( 3 ulasan ) Lund, Sweden

ID Projek: #21291917

11 pekerja bebas membida secara purata €30 untuk pekerjaan ini


Dear Employer, I have checked the google sheet and i can write google script to calculate report [login to view URL] i have written similar time sheet reporting scripts. Let me send them to you. I hav Lagi

€35 EUR dalam 2 hari
(422 Ulasan)

I have huge Google Sheet script experience that's why I'm sure that you'll be impressed with my work. I can provide you a simple to read script that will work exactly like you want (copy raw data into another tab and p Lagi

€80 EUR dalam sehari
(150 Ulasan)

Hi, I have read the project details very carefully and I understand that I have to implement a complete working hours calculation algorithm on the basis of provided rules and by performing operations on the data simila Lagi

€30 EUR dalam 2 hari
(12 Ulasan)

Hi Sir, I have gone through your description and I am able to do your job in about no time according to the job description. What you will get? * You get standard quality work * You will get technical support(After Lagi

€30 EUR dalam 3 hari
(7 Ulasan)

Welcome to your job! I've just got the general idea of your needs. you can check my review with javascript. I'd like to get more details in chat. I have rich experiences and flexible skills in Javascript. I can start r Lagi

€19 EUR dalam 7 hari
(6 Ulasan)

All of our programming skills: *Front-end: - HTML, HTML5, JSON. - JAVASCRIPT (Ajax, AngularJS / 2 / 4 / 5 / 6 / 7, ReactJS - Redux, Jquery). - CSS, CSS3, Twitter Bootstrap, Less, Sass, Scss, Responsive, Material. - M Lagi

€30 EUR dalam sehari
(3 Ulasan)

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

€25 EUR dalam 3 hari
(3 Ulasan)

Hello, I am aable to do this job with javascript or excel, the both are easy for me. Be confident.

€19 EUR dalam 2 hari
(0 Ulasan)

Hey , looking forward to work with you. Relevant Skills and Experience Data entry

€19 EUR dalam 2 hari
(0 Ulasan)

I am willing to do your work in a good and responsible manner with excellent quality work.

€23 EUR dalam sehari
(0 Ulasan)

I am currently a pharmacy student in Milwaukee, Wisconsin looking for freelance work to help with income. Although not officially licensed, I have a passion for graphic and website design. I have also used most photo e Lagi

€25 EUR dalam 2 hari
(0 Ulasan)