Time Sheet Calculation

[Laura V]

I am trying to set up a time sheet to track employee hours. I am stuck
with calculating the total time, because my company rounds the hours.
For instance, if you work a shift from 1 p.m. and leave at 5:12 pm, you
would receive pay for 4.2 hours. Here is the time table (in minutes):

0 - 5 = .0
6 - 11 = .1
12 - 17 = .2
18 - 23 = .3
24 - 29 = .4
30 - 35 = .5
36 - 41 = .6
42 - 47 = .7
48 - 53 = .8
54 - 59 = .9
60 = 1.0

Is there a formula that would assist me?
Any help or direction would be great!
Thanks!!

 

[Frank Kabel]

Hi
how do you enter your time values (Ecel time format?)

 

[David McRitchie]

Laura showed the time in as hr:min for working time, so
I would expect that is how they fill in the time sheets.

I believe this formula matches the table
=ROUNDDOWN(B1*24,1)
where B1 is entered as Excel time hh:min

If you are totaling Excel time use a format of [h]:mm
to keep hours from overflowing into days.
But I think you would not be totalling thtat column but
the hours rounded down to tenths.

Time and days are measured in units of days past a certain date.
For most Excel users that would be 1900 date system, days
past Dec 31, 1899 More on Date and Time in
http://www.mvps.org/dmcritchie/excel/datetime.htm

The 1904 date system is also available to everyone, but is
mainly used by those on Mac machines, and the 1900 mainly
by those on PCs.

Most questions on time would probably be answered there
Rounding down was not one of them.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[Laura V]

Okay guys, sorry I haven't gotten back with you - I don't know how t
find my postings so I can check them.

In regards to the time sheet, I have the cells formatted as hh:mm:ss.
When I enter the values in Excel, I simply enter: 8:00 am or 2:03 pm
I can get the column to total, but I cannot get Excel to subtract th
start and end times in each row. I need to have Excel calculate th
time according to that chart above. When I tried using the rounddow
function for 8:03 am to 2:00 pm = 5.9, but I want it to calculate t
6.0


Is there any way to use the IF function with this?

I am really struggling. Thanks so much for your help,
Laura

 

[David McRitchie]

You posted as
From: Laura V <<[email protected]>>
From: Laura V <<[email protected]>>

with the typical screwed up from headers of excelforum.com
having extra "<" and ">". In order to use Google Groups search
you should use a consistent email address, so you can search on
it as the best means of identifying yourself.

To search in Google Groups (12 hour delay) you can use
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

the by author name: (e-mail address removed)
or author name: Laura V

Which will locate all "threads" you posted to. (if you had a consistent email
address), Or in the second case anyone named Laura V , or V Laura,
or something consisting of those names., and usage of a name is not as
reliable (unque) as an email address though restricting to Excel groups helps.

Suggest you learn to use the facilities that you choose to use, most of us
still prefer to look at newsgroups without advertising.

The ROUNDDOWN function is very much like truncate, I think you just
want to use the ROUND function.



HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm