Solved

Time card application

Posted on 2014-04-04
8
221 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Article by: Nadia
Linear search (searching each index in an array one by one) works almost everywhere but it is not optimal in many cases. Let's assume, we have a book which has 42949672960 pages. We also have a table of contents. Now we want to read the content on p…
Iteration: Iteration is repetition of a process. A student who goes to school repeats the process of going to school everyday until graduation. We go to grocery store at least once or twice a month to buy products. We repeat this process every mont…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
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.

707 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

12 Experts available now in Live!

Get 1:1 Help Now