Solved

help with excel needed

Posted on 2013-11-21
16
111 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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
 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

770 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