Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
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.
I hate time calculations in Excel.
Here's what I tried.
A1: 8:11 AM
B1: 4:58 PMC1: =(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?
0 
Time IN Lunch Out Lunch In Time out Total Tot Out Actual Paid
Sun
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
Fri
Sat
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.
0
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?
0
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.
0
I am still looking for help on response #4 if anyone is able to come up with a solution I would be very grateful!
0
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.
0
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.
0
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.
0
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 OKThat should help correct formatting errors and format the totals in a readable format.
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
