Automatic timesheet for all employees

JakkeJakobsen

Board Regular
Joined
Sep 10, 2014
Messages
94
Hi! I collect all the data (using Google Sheets, so it is all connected and cloud-based) from my employees' timesheets into one. The reason is to see where there are holes in the timesheets, and to see who worked when in an easy way (we got set shifts that doesn't need a schedule).

Now, I got date, start and stop times for each person like this, sorry for this being in Norwegian, no way around that I'm afraid:
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
Lør16/12/2017
Søn17/12/2017
Man18/12/201717:0022:00
Tirs19/12/201717:0022:00
Ons20/12/2017
Tors21/12/2017
Fre22/12/2017
Lør23/12/2017
Søn24/12/2017
Man25/12/2017
Tirs26/12/2017
Ons27/12/2017
Tors28/12/201717:0022:00
Fre29/12/201715:0023:00
Lør30/12/2017
Søn31/12/2017
Man01/01/2018
Tirs02/01/201817:0022:00
Ons03/01/201817:0022:00
Tors04/01/201817:0022:00
Fre05/01/201817:0021:0021:0000:00

<colgroup><col style="width: 41px"><col width="77"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"></colgroup><tbody>
</tbody>
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Now, what I want, is to fill this somehow, automatically:
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
Dag
Dato
09:00
10:00
11:00
12:00
13:00
14:00
15:00
16:00
17:00
18:00
19:00
20:00
21:00
22:00
23:00
00:00
01:00
02:00
03:00
04:00
05:00
06:00
07:00
08:00
Lør16/12/2017
Søn17/12/2017
Man18/12/2017
Tirs19/12/2017
Ons20/12/2017
Tors21/12/2017
Fre22/12/2017
Lør23/12/2017
Søn24/12/2017
Man25/12/2017
Tirs26/12/2017
Ons27/12/2017
Tors28/12/2017
Fre29/12/2017
Lør30/12/2017
Søn31/12/2017
Man01/01/2018
Tirs02/01/2018
Ons03/01/2018
Tors04/01/2018
Fre05/01/2018

<colgroup><col style="width: 41px"><col width="77"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"><col width="50"></colgroup><tbody>
</tbody>
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
[FONT=Inconsolata, monospace, arial, sans, sans-serif]Sorted! Added the raw input data into the sheet with one cell per hour at A:H, and did this:

[/FONT]=if(and($C75<I$1,$D75>=I$1),"Name1",if(and($E75<I$1,$F75>=I$1),"Name2",if(and($G75<I$1,$H75>=I$1),"Name3","")))
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top