asked on

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

SOLUTION

NBVC

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ReneGe

ASKER

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

SOLUTION

nick2253

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

NBVC

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ReneGe

ASKER

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

SOLUTION

Member_2_276102

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ReneGe

ASKER

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