Solved

help with excel needed

Posted on 2013-11-21
16
113 Views
Last Modified: 2014-01-15
this is the format i get my information on:
csv formatand that's what i'd like to calculate:
1. total days worked (count the different dates, exclude missing days of course).
2. total hours per day.
3. how many breaks and total breaks time per day.

any suggestion will be highly appreciated!
0
Comment
Question by:C8967349
  • 7
  • 7
  • 2
16 Comments
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39666437
I believe that is possible but can you upload sample data rather than a screen shot?

We then have data to use rather than having to replicate it.

Thanks
Rob H
0
 

Author Comment

by:C8967349
ID: 39666453
sample attached,
thanks for your good will.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39666457
Upload unsuccessful
0
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 

Author Comment

by:C8967349
ID: 39666463
sorry about that, now it should be ok.
total-hours.csv
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39666531
Does true data come in this format, stumbling across slight issues with format of date and time; they are showing as text.

I can get round it for the sample but aware that this may be a bigger issue with full data set.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39666570
You might like to convert the data into a pivot table and then work on it. As robhenson mentioned you can convert the textual values to numeric values before it can be comprehended by the pivot table. See attached.
Total-hours.csv
0
 

Author Comment

by:C8967349
ID: 39666588
robhenson - that's a real export from the software, i only changed the employee name to "employee".
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39666646
Couple of anomalies in the data that you might like to clean up first.

8 Nov - Two out entries at 5:00 and 6:31, should 5:00 be "in" as went to lunch at 4:00pm
12 Nov - Two in entries at 9:31

With assumptions above and corrected, see attached.

The DATEVALUE formula should work correctly in your locale so shouldn't have to fudge it for different locales like I have to get the dates correct but if you look at the "Dec Hrs" & "Hrs Worked" columns on the UK tab it should give you an idea on how to do it. I have then made a pivot on the calculated data.

Thanks
Rob H
total-hours.xlsx
0
 

Author Comment

by:C8967349
ID: 39671483
Rob, that's awesome, many thank for all your help!

The only change I'd really like to add to "UK" tab is highlight the time the employee was out at lunch or break, counting from lunch\break until the next in, break or lunch, that will start a new count so it also needs to stop a previous one (:

lunch can be bold blue and break bold orange just to distinguish between them.

Thanks again!
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39683407
Apologies for delay, see attached.

Not sure I fully understand the additional requirement but have added a couple of columns to the data and the Pivot.

The Lunch and Break columns assume, maybe incorrectly, that the time stamp following the lunch/break stamp will be the same day. This is based on assumption if the employee is taking lunch/break they will be returning later the same day otherwise they would just clock out.

As a sanity check of the data, I have also added Arrival and Departure times. These can be easily removed from the Pivot by right clicking on the column header and choose Remove column from the popup menu.

Hope this helps.

Thanks
Rob H
total-hours.xlsx
0
 

Author Comment

by:C8967349
ID: 39696074
Hi Rob, thanks for the great work,
Unfortunately I'm not sure how the latest excel file works,
For example, when I manually edit the break time on Nov 11th (under the UK tab), it doesn't change any field value other then the "break" column on the same line, but this field is in an unreadable format (full "time", e.g 00:00:00 am).
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39696082
Can you upload your file with amendment so that I can see what you mean?

Thanks
Rob H
0
 

Author Comment

by:C8967349
ID: 39696086
I just downloaded your latest attachment and tried to understand how I fill up new values (to calculate other employee's times).

I'm attaching to this message another excel file from our time clock, please help me understand how to replace the values on your excel with the ones I exported.

Again, many many thanks!
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39696101
No attachment. Can you just attach the excel file with your Nov 11th amendment highlighted.

Thanks
Rob H
0
 

Author Comment

by:C8967349
ID: 39696109
and as always i forgot to actually attach the file (:
example2.csv
total-hours.xlsx
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39697849
Not sure what is happening with the change on the 11 Nov data.

When I look at that file, the break column has changed to 00:13:00; this is correct because start break at 9:08 and clock back in at 9:21 giving a 13 minute break. On the Pivot tab when I refresh the Pivot Table (right click on table, select Refresh) the numbers change there as well.

Whats happening at your end?

For additional data, you will need to paste in the extra data and then copy the formulas in columns E to J down.

To include the extra data in the Pivot Table, you need to extend the range that the PT is looking at.

When the cursor is in the PT a couple of extra tabs appear on the Ribbon Bar for "Pivot Table Tools". Select the Options tab and there is an option for "Change Data Source". Click this and use the Range selector to choose the new data set.

Alternatively, you can get round having to do this each time by making the Data Set a dynamic Named Range.

In the Name Manager (Formulas tab) create a Range Name called PT_Data. For the refers to enter the following formula:

=OFFSET(UK!$A$1,0,0,COUNTA(UK!$A:$A),COUNTA(UK!$1:$1))

This creates a range that starts at UK!$A$1 and is the same height and width as the data on the UK tab. If you called the tab something else then amend the "UK!" bits accordingly.

Then in the Change Data source window, use =PT_Data instead of the absolute range.

Hope this helps,
Thanks
Rob H
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question