formula to determine what time double time starts and ends after 16:30

hi
I have a spreadsheet that is a timesheet,have formula that starts at 00:00 and goes to 16:30, 00:00 to 06:00 is double time ,07:00 to 08:00 is time and half
this works ,but if I try to enter after 16:30 it goes all wrong, how can I do a formula that starts after 16:30 to 24:00,that takes into account 16:30 to 1900 is time and half and 1900 to 24:00 is double,can excel determine what time it is and what rate it should be looking at
cheers colin

hi
have attached book for you to look at thankyou,any pointers you can give me would be good,this is part of a bigger program just wanted the workings out to be separate as others will use the timesheet entry and monthly totals,can upload this if you want
cheers colin

Pl see the attached file.

How it is 8:00 , Is there lunch break, if so What is the timings. 7:00 to 16:30 is 8 hrs + 1:30 hrs according to your explanation in first post, how it is 9 Hrs.

hi
yes there is lunch break 12:00 to 12:30 , but even at that if you take that out, 07:00 to 16:30 should be 9 and comes out at 9:30 hrs
cheers colin

9:30 is total effective hours, because 7 to 8 is 1:30 and 8 to 16:30 is 8 Hrs .Total 8+1:30= 9:30 Hrs.

hi
7-8 is one hour and 8-1630 is 8hrs as you have said total should be 9hrs and not 930 ,hope this helps,could you give me a bit of a break down on formula, will have to do one for sat and sun and can not get to grips with what you have done try as I might please
cheers colin