Solved

help with excel needed

Posted on 2013-11-21
16
110 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 32

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
 

Author Comment

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

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 32

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 32

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 32

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 32

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 32

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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…

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now