Solved

help with excel needed

Posted on 2013-11-21
16
114 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

739 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