• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

Time card application

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
ReneGe
Asked:
ReneGe
5 Solutions
 
NBVCCommented:
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
 
ReneGeAuthor Commented:
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
 
nick2253Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
NBVCCommented:
Can you introduce another column showing the difference between Punch Out and Punch In?  Then you can use that column in your PT.
0
 
Ejgil HedegaardCommented:
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
 
ReneGeAuthor Commented:
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
 
tliottaCommented:
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
 
ReneGeAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now