Solved

Time card application

Posted on 2014-04-04
8
223 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
8 Comments
 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 67 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 33 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
 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 67 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
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.

 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 200 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 200 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

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Foreword (May 2015) This web page has appeared at Google.  It's definitely worth considering! https://www.google.com/about/careers/students/guide-to-technical-development.html How to Know You are Making a Difference at EE In August, 2013, one …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

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

20 Experts available now in Live!

Get 1:1 Help Now