?
Solved

Time card application

Posted on 2014-04-04
8
Medium Priority
?
232 Views
Last Modified: 2014-04-15
Hi there,

I have a time card database and I need to create a report for payroll in Excel.

This report needs to list for each CARD, how many hours (In decimal. For example 8.75h) an employee worked, listed by day.

Please note that the time here is in DECIMAL. So for example, 7.75 = 7h45

CARD       DATE            PUNCH_TIME
100      2014-01-01      8.00
100      2014-01-01      10.00
100      2014-01-01      10.25
100      2014-01-01      12.00
100      2014-01-01      12.50
100      2014-01-01      17.00
100      2014-01-01      7.75
100      2014-01-01      10.00
100      2014-01-01      10.25
100      2014-01-01      12.00
100      2014-01-01      12.50
100      2014-01-01      17.00
101      2014-01-01      8.5
101      2014-01-01      10.00
101      2014-01-01      10.25
101      2014-01-01      12.00
101      2014-01-01      12.50
101      2014-01-01      17.00
101      2014-01-01      8.75
101      2014-01-01      10.00
101      2014-01-01      10.25
101      2014-01-01      12.00
101      2014-01-01      12.50
101      2014-01-01      17.00

Thanks for your help!

Cheers,
Rene
0
Comment
Question by:ReneGe
[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
8 Comments
 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 268 total points
ID: 39978554
Try a Pivot Table.

Select the range, go to Insert >> Pivot Table.

Click Next, then drag Date to Row area, then drag Card to Row area, then drag Hours to Sum Values area.

You can re-arrange the Date and  Card if you want to table it differently.
0
 
LVL 10

Author Comment

by:ReneGe
ID: 39978581
Thanks NBVC for your prompt reply.

Where i was unable to use Pivot Tables here, is that for every punch, there is an IN and an OUT.

So for example, if someone punches at 8AM, then at 9AM, Then at 4PM, then at 5PM, that person would have worked for only 2hours that day.  However, the Pivot table should show 8+9+16+17. Right?

Is there a way to get this in a Pivot table?

Thanks and cheers,
Rene
0
 
LVL 9

Assisted Solution

by:nick2253
nick2253 earned 132 total points
ID: 39978623
You probably can't do this directly in excel (or, at least, you won't be able to do it without VBA stuff).

I would do this programmatically using VBA in excel.  I'd think that's the easiest way to turn your data into the data you want.
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 268 total points
ID: 39978628
Can you introduce another column showing the difference between Punch Out and Punch In?  Then you can use that column in your PT.
0
 
LVL 23

Accepted Solution

by:
Ejgil Hedegaard earned 800 total points
ID: 39978773
I agree with NBVC, a pivot table is the easiest way.
Make a column D for In/Out with this formula
=IF(OR(A2&B2<>A1&B1,D1="Out"),"In","Out")
An at column E for the time difference, with this formula
=IF(D2="Out",C2-C1,0)
Then the time Card, Date and Time difference can be used in a pivot table.

If data always are like shown (guess half should be date 2014-01-02), then a simpler formula for time, without the In/Out column
=IF(ISODD(ROW()),C2-C1,0)
But if only one time stamp is missing then all will be wrong, where the above method is wrong only for that card that day.
0
 
LVL 10

Author Comment

by:ReneGe
ID: 39980252
Hey guys,

Thanks for all your feedbacks and ideas.

I actually need to analyse and make a report on the whole database. Therefore, there are a lot a dates, punch entries and card numbers.

I understand that missing punches must be identified and fixed before generating the report.

@hgholt
You introduced functions to me I never used in Excel. Thanks.

Im 'replying from my phone. I'll update you latet.

Thanks and cheers,
Rene
0
 
LVL 27

Assisted Solution

by:tliotta
tliotta earned 800 total points
ID: 39995796
Are there known rules that you need to account for? For example, there should be an even number of punches per card per day, and no In/Out time for a given day and card can be earlier than any In/Out time from earlier rows for that day and card.

(The data is clearly a problem in the example as noted by hgholt, but we can probably assume that that's just due to creating sample data and will be fixed.)

If there are rules that must be (or even may be) applied, it could be easier to specify processing. Any rules you can list would be helpful.

Tom
0
 
LVL 10

Author Closing Comment

by:ReneGe
ID: 40002960
Hi all,

Thanks for your contributions.

I have not still concluded this but I have ennough clues to bring this project to life.

I have almost completed it in Excel, with a few extra rules to include.  However, I need a more robust platform.  VBA is not a solution for me because I never implement something I do not have the knoledge to support, nor wish to learn (I'd rather spend my time mastering Java).

For a short term solution, I plan to use File Maker Pro.  It's been a while I have not used it so I need to updae myself (A week or two investment).

Thanks and cheers to you all,
Rene
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When there is a disconnect between the intentions of their creator and the recipient, when algorithms go awry, they can have disastrous consequences.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

771 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