Excel Time card function :: MrExcel Message Board
 Search   Memberlist   Usergroups   Favorites   Statistics   Register

 Time card function

c24c4
Board Regular

Joined: 31 May 2002
Posts: 6

Status: Offline

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 ]

Sat Jun 01, 2002 10:00 am

Dexter
Board Regular

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

Status: Offline

Sat Jun 01, 2002 10:24 am

Dexter
Board Regular

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

Status: Offline

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.

Dexter

Sat Jun 01, 2002 10:27 am

c24c4
Board Regular

Joined: 31 May 2002
Posts: 6

Status: Offline

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

Curt

Sat Jun 01, 2002 10:34 am

Asala42
Board Master

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

Status: Offline

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 ]

Sat Jun 01, 2002 10:38 am

c24c4
Board Regular

Joined: 31 May 2002
Posts: 6

Status: Offline

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 ]

Sat Jun 01, 2002 10:53 am

Asala42
Board Master

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

Status: Offline

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.

Sat Jun 01, 2002 12:08 pm

.

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

Status: Offline

 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
 C3D3E3F3G3C4D4E4F4G4C5D5E5F5G5C6D6E6F6G6 =

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.

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 ]

Sat Jun 01, 2002 12:09 pm

Yogi Anand
MrExcel MVP

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

Status: Offline

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

Sat Jun 01, 2002 4:49 pm

c24c4
Board Regular

Joined: 31 May 2002
Posts: 6

Status: Offline

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

Spl thanks to Aladin Akyurek and Yogi Anand

Curt

Sun Jun 02, 2002 12:21 pm
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

Forum Jump:
 Jump to: Select a forum MrExcel Forums----------------Excel QuestionsInternational forumMicrosoft AccessTechnical issues and Future development Holy Macro! Books----------------Holy Macro! Products The Lounge----------------Max Cells Lounge Announcements----------------About This BoardHall of Fame WinnersTest Here

Page 1 of 1

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