Creating a Time card in Excel

May 2, 2009 at 19:08:07
Specs: Windows Vista
I am trying to create a time card that will allow me to look at my hours and determine at the end of the week how many hours I have to work to get to 40 hours. Currentley we are using a Kronos time system for our company and everything is based in .0 of hours. Here is a typical time card IN 8:11am. lunch 30 minutes, and out at 4:58pm. The times varie from day to day so on friday I would like like to be able to punch in the 4 previous times and come up with a answer such as 7:22 or what ever the case may be as to what it will take to make 40 hours. Any help in the matter would be appreciated.

See More: Creating a Time card in Excel

Report •

Report •

May 2, 2009 at 21:34:35
I hate time calculations in Excel.

Here's what I tried.

A1: 8:11 AM
B1: 4:58 PM

C1: =(B1-A1)*24-.5 Result: 8.28

Repeat that for 3 more working days.

In C5 I put: =40-SUM(C1:C4)+0.5 Result: 7.37

Does that come out to what you think it should, working those same hours for all 4 days?

Report •

May 2, 2009 at 23:26:06
Time IN Lunch Out Lunch In Time out Total Tot Out Actual Paid

Mon 8:11 AM 12:00 12:30 4:58 PM 8:47 0:30 8:17
Tue 8:30 AM 12:00 12:30 5:02 PM 8:32 0:30 8:02
Wed 8:31 AM 12:00 12:30 5:16 PM 8:45 0:30 8:15
Thurs 8:23 AM 12:00 12:30 4:38 PM 8:15 0:30 7:45
This is what I have come up with so far.
Total Calculation =TEXT(E6-B6,"h:mm")
Total Out Calc. =TEXT(D6-C6,"h:mm")
Actual paid calc =TEXT(F6-G6,"h:mm")

Now what I would like to do is be able to add the 4 actual paid hours up and determine how many hours and minutes I need to get to 40 hours even. In this example it should be 7 hours and 41 Minutes.

Report •

Related Solutions

May 3, 2009 at 23:27:02
I am still having issues with the final calculation of adding the 4 total paid hours up and then subrtacting this from 40 hours to give me the time required to make the 40 hours. Any suggestions?

Report •

May 4, 2009 at 08:52:41
Try this: 40/24-SUM(times)

There might be some formatting issues to deal with, but I managed to get 7:41 with that formula.

Report •

May 6, 2009 at 02:03:55
I am still looking for help on response #4 if anyone is able to come up with a solution I would be very grateful!

Report •

May 6, 2009 at 07:30:47
What is wrong with the suggestion by DerbyDad03?

You might also consider Custom Formatting all of your Total cells to [H]:MM.
That should remove the need to use a =TEXT formula.


Report •

May 6, 2009 at 09:53:53
Have you read/tried Response # 5?

Report •

May 6, 2009 at 23:06:24
I have tried to format the lines to add all the times together. When doing so I am getting an error that I am unable to resolve. I then have tried to use the calculation and do not understand the formating on it also so again I am recieving errors. I understand the formula but I need help in making it into a usable format. Thanks for the help.

Report •

May 7, 2009 at 06:49:43
Let me see if I can come up with a short synopsis of your most recent post:

"I'm getting errors. Please help."

Since I can't see your machine from where I'm sitting, am I supposed to simply guess at what errors you are getting and come up with a solution based on that guess?

Bottom line:

Whether I manually type in your example "Actual Paid" times into a series of cells or calculate those times by using =B1-A1, where B1 and A1 contain times, I can use =40/24-SUM(times) and get the results you want.

Granted, by simpling entering =40/24-SUM(times) in a cell I will get 0.320138889 from your example, but as soon as I format it as [h]:mm I'll get 7:41.

I really don't know what else to tell you.

Report •

May 7, 2009 at 07:09:22
The formatting for all of the Total cells is [H]:MM:SS

To format all of your Total cells
First select all of the cells that will contain totals
Then on the Task Bar
Select Format
Select Cells
In the small window that pops up
Select Custom (Down at the bottom of the list)
When you select custom a scrollable
window will appear on the right.
Scroll down until you find the format [H]:MM:SS
IMPORTANT: It must be the format with the two Braces around the letter H, do not use just a plain H:MM:SS
Select the format
Click OK

That should help correct formatting errors and format the totals in a readable format.


Report •

Ask Question