Time card function

Thanks:  0
Likes:  0

1.
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 ]

2.

3. 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

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

Curt

5. 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 ]

6. 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 ]

7. 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.

8.  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 ]

9. 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!

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

Spl thanks to Aladin Akyurek and Yogi Anand

Curt

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•