Solved

help with excel needed

Posted on 2013-11-21
16
109 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 31

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 31

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 31

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 31

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 31

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 31

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 31

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

708 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

13 Experts available now in Live!

Get 1:1 Help Now