Excel Time card function :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Time card function
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

c24c4
Board Regular


Joined: 31 May 2002
Posts: 6


Status: Offline

 Reply with quote  

I am repeating the question since I did not see it posted to the board.

I would like to create a time card sheet that has time in and time out, then caculate the number of hours worked (this I can do) then convert the mm to 1/4 hour increments i.e. 0 to 15 minutes = .25, 16 to 30 minutes = .5, 31 to 45 minutes = .75, 46 to 60 minutes = next whole hour.

Even better if we can incorporate the 8 minuite rule (7 min or less is rounded down and 8 min or greater is round up to the next 1/4 hour.

I will take the output of this function and multply by the pay rate to get gross pay.

Thanks for the help, Curt

[ This Message was edited by: c24c4 on 2002-06-01 06:01 ]

Post Sat Jun 01, 2002 10:00 am 
 View user's profile Send private message

Dexter
Board Regular


Joined: 04 Apr 2002
Posts: 73
Location: St. Louis

Status: Offline

 Reply with quote  

Post Sat Jun 01, 2002 10:24 am 
 View user's profile Send private message

Dexter
Board Regular


Joined: 04 Apr 2002
Posts: 73
Location: St. Louis

Status: Offline

 Reply with quote  

I built a spreadsheet years ago in Lotus.
I addressed your problem of the "7 minute" rule via a lookup table. The file can be loaded into Excel where you can examine the formulas.

Although there are macros in it to control pointer movement, they are not necessary to accomplish what you want and can be ignored.

My email: droodhall@earthlink.net

Dexter

Post Sat Jun 01, 2002 10:27 am 
 View user's profile Send private message

c24c4
Board Regular


Joined: 31 May 2002
Posts: 6


Status: Offline

 Reply with quote  

Dexter you did not attach the lookup table if that was your intent.

Curt

Post Sat Jun 01, 2002 10:34 am 
 View user's profile Send private message

Asala42
Board Master


Joined: 27 Feb 2002
Posts: 787
Location: Tampa, FL USA

Status: Offline

 Reply with quote  


quote:

I would like to create a time card sheet that has time in and time out, then caculate the number of hours worked (this I can do) then convert the mm to 1/4 hour increments i.e. 0 to 15 minutes = .25, 16 to 30 minutes = .5, 31 to 45 minutes = .75, 46 to 60 minutes = next whole hour.



What is the end format of your #of hours calculation (for example is 8 hours 15 minutes showing as 8:15 (time format) or 8.25 (number format))

If number format (8:13 = 8.216667)

=ROUND(C4*4,0)/4

or if a time format (8:13 = 8:13:00 AM)

=ROUND(C4*96,0)/(96)

Where C4 holds your existing hour calculation.

[ This Message was edited by: Asala42 on 2002-06-01 06:39 ]

Post Sat Jun 01, 2002 10:38 am 
 View user's profile Send private message Send e-mail AIM Address

c24c4
Board Regular


Joined: 31 May 2002
Posts: 6


Status: Offline

 Reply with quote  

It is in time format hh:mm since I amusing the 24 hour format for the time card entries
and the caculation.
I could not seem to get a reasable number formate out of the function.

curt

[ This Message was edited by: c24c4 on 2002-06-01 07:04 ]

Post Sat Jun 01, 2002 10:53 am 
 View user's profile Send private message

Asala42
Board Master


Joined: 27 Feb 2002
Posts: 787
Location: Tampa, FL USA

Status: Offline

 Reply with quote  


quote:

It is in time format hh:mm since I amusing the 24 hour format for the time card entries
and the caculation.
I could not seem to get a reasable number formate out of the function.



You can convert between the 2 formats using a multiplier of 24 (time format 8:15*24 = 8.25 in a number format)

Did something go wrong with the above formula? If so, specify your formula and the results.

Post Sat Jun 01, 2002 12:08 pm 
 View user's profile Send private message Send e-mail AIM Address

Aladin Akyurek
.


Joined: 15 Feb 2002
Posts: 13437
Location: The Hague
Flag: Blank

Status: Offline

 Reply with quote  

Microsoft Excel - aaTimeCard c24c4.xls___Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
=

A
B
C
D
E
F
G
1





$6.00
2
Time InTime OutHours workedRoundedDecimal timeGross payAll in 1 formula
3
8:3014:205:505:455.75$34.50 $34.50
4
9:0014:105:105:155.25$31.50 $31.50
5
9:0013:044:044:004$24.00 $24.00
6
10:0015:285:285:305.5$33.00 $33.00
Sheet1

To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The above image was automatically generated by [HtmlMaker 2.0] If you want this FREE SOFT, click here to download
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


[ This Message was edited by: Aladin Akyurek on 2002-06-01 08:14 ]

Post Sat Jun 01, 2002 12:09 pm 
 View user's profile Send private message

Yogi Anand
MrExcel MVP


Joined: 13 Mar 2002
Posts: 5440
Location: Michigan USA

Status: Offline

 Reply with quote  

Hi c24c4:

For rounding the time to your specification, you can use the MROUND function, let us say Start time is 8:30 and Leave Time is 14:20

then =MROUND("14:20"-"8:30","0:15") ... will give you 5:45 -- formated as Number 5.75

and if StartTime is 8:30 and LeveTime is 14:23

then =MROUND("14:23"-"8:30","0:15") ... will give you 6:00, and formatted as number 6.0

Of course you should enter the StartTime and LeaveTime as Cell references. Also please note that to be ble to use the MROUND function, you have to have the Analysis ToolPak checked through TOOLS|ADD_INs

Regards!

_________________
Regards!
Yogi Anand

Post Sat Jun 01, 2002 4:49 pm 
 View user's profile Send private message

c24c4
Board Regular


Joined: 31 May 2002
Posts: 6


Status: Offline

 Reply with quote  

Thanks to all for all the help, it was more than expected.

Spl thanks to Aladin Akyurek and Yogi Anand

Curt

Post Sun Jun 02, 2002 12:21 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.