Time card function

c24c4

New Member
Joined
May 30, 2002
Messages
6
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
Thanks to all for all the help, it was more than expected.

Spl thanks to Aladin Akyurek and Yogi Anand

Curt
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top