Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Time card application

Posted on 2014-04-04
Medium Priority
236 Views
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
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

LVL 23

Assisted Solution

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

ID: 39978581

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

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

LVL 23

Assisted Solution

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

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

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

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

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

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month11 days, 20 hours left to enroll

#### 636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.