excel formulas

[mick_roger]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi

I would like to know how to enter a formula to perform the following function:

the form is a time sheet to calculate hours. i have a time in and time out column. i want to have a column that calculates the hours worked but with a function that calculates what rate to multiply the hours worked by e.g. 07:00-16:00 is normal 9.00 hours but 23:30-07:30 is time and a half so will have a total of 12 hours.

many thanks

mick

 

[John McGhie]

Arrange four columns:

Columns F, G, and H.

Start time, Finish time, Hours worked

In Hours worked (Column H), the formula is:

=IF(F10=0,0,IF(G10=0,0,IF(G10<F10,G10+12-F10,G10-F10)))

That gives you an Excel TIME in hours and minutes, whether the working time
spans midnight or not.

To determine how many hours were spent either side of the time-and-a-half
line, subtract the start time from the start of T1.5 to give you T1 hours,
then subtract that from Total hours to give you the T1.5 hours.

Hope this helps

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi

I would like to know how to enter a formula to perform the following function:

the form is a time sheet to calculate hours. i have a time in and time out
column. i want to have a column that calculates the hours worked but with a
function that calculates what rate to multiply the hours worked by e.g.
07:00-16:00 is normal 9.00 hours but 23:30-07:30 is time and a half so will
have a total of 12 hours.

many thanks

mick

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[mick_roger]

thanks for the advice. ill give it a go and if you dont hear from me it means you are a fantastic teacher and i have amazed myself by getting something done.