A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Excel formula for a time sheet



 
 
Thread Tools Display Modes
  #1  
Old August 10th, 2005, 02:36 PM
HRMSN
external usenet poster
 
Posts: n/a
Default Excel formula for a time sheet

I have designed a time sheet for payroll purposes that automatically
calculates all appropriate formulas. The only one I haven't figured out yet
is this: in my office, we follow the seven minute rule in that when
calculating time, every clock in and clock out time is rounded to the nearest
quarter hour. Currently, my time sheet sums up the total time for each day.
I need a formula that then rounds that total time that is listed to the
nearest quarter hour (whether up or down). Can anyone help me out.
  #2  
Old August 10th, 2005, 03:07 PM
Bob Phillips
external usenet poster
 
Posts: n/a
Default

Assuming that the sum is in A1

=ROUND(A1*24*4,0)/4/24

The multiply by 24 is to convert to decimal hours, the 4 is to convert to
decimal quarter hours. This is rounded to the nearest whole number, then
converted back to time. You should see that this can be simplified to

=ROUND(A1*96,0)/96

--

HTH

RP
(remove nothere from the email address if mailing direct)


"HRMSN" wrote in message
...
I have designed a time sheet for payroll purposes that automatically
calculates all appropriate formulas. The only one I haven't figured out

yet
is this: in my office, we follow the seven minute rule in that when
calculating time, every clock in and clock out time is rounded to the

nearest
quarter hour. Currently, my time sheet sums up the total time for each

day.
I need a formula that then rounds that total time that is listed to the
nearest quarter hour (whether up or down). Can anyone help me out.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to deduct unpaid breaks in time sheet Rick General Discussion 3 August 26th, 2005 11:53 PM
Does excel recognise names rather than cells? Sue Worksheet Functions 9 May 22nd, 2005 04:51 AM
Please Help! Ref Article ID 210504 Telobamipada New Users 8 May 9th, 2005 01:09 PM
Am trying to make a time sheet and having problems with a formula. godnight Worksheet Functions 2 February 14th, 2005 02:21 PM
Use first record found in expression? CASJAS Running & Setting Up Queries 17 July 22nd, 2004 09:21 PM


All times are GMT +1. The time now is 03:57 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.