Formula difficulty

[Pyrite]

Hi,

I've done quite well at keeping my name off these boards lately and have
been figuring things out, however, I am pretty stuck with this one.

The situation is that i am making a timesheet, users will enter the time
they start travelling and the time this finish. The time they start on site
and the time they finish. The time they start travelling and the time they
finish. For the first 8 hours they get paid single time, anything above that
they get paid 1.5 time. I need to be able to total the hours for the day,
minus .5 hours (30 minutes) for lunch time and then have the total hours
sorted between the 1x column and the 1.5x column. Therefore the 1x column
should never be more than 8. Not to complicate things further but Travel is
always paid at 1x. I am looking for formulas in the 1x and 1.5x columns (or
maybe just 1x if thats all that is needed).

Example:

A B C D
E F
Travel Start Travel Finish Site Start Site Finsh
Hours 1x Hours 1.5x
08:00 09:00 09:00 18:00
8 1.5
18:00 19:00
1

On the first line there is 10 hours, but the half hour lunch needs minussing
givving 9 1/2 split 8 hours and 1.5 hours. The second line is the travel home
so i single time. It will also need to look at total hours for the day as
someone may visit more than one site, only one half hour should be deducted
though. I think it will probably be better to have a daily total in the 1x
and 1.5x columns instead of trying to get it to work it out as displayed
above on an indiviual entry basis.

A bit of a puzzler indeed )

Thanks in advance for any help or guidance you can offer.

 

[Pyrite]

Ive been thinking on this some more and I think I have got somewhere. If I
use a daily total then the hours 1x can be more than 8 because travel is
included but only ever paid at single time. What I could do is add the travel
time into the total column regardless, I could then (somewhere else on the
sheet) total the site time and have a formula that says if the site time is
less than 8 add to the total column, if it is more than 8 add the 8 to the
total column and put the left over in the 1.5x column.

I just dont know how to tell a formula to split numbers like that i.e. if
over 8 put the remainder somewhere and the 8 somewhere else.

 

[Pyrite]

Here is what I now have.

In the 1x column I have =(D3-C3)+(IF((F3-E3)<=8,(F2-E3),8))

This has travel finish in Column D, travel start in column C, Site finish in
column F and site start in column E.

In the 1.5x column I have =IF(F3-E3>8,(F3-E3)-8,"")

Now I just need to extend it to incorporate multiple rows for each day, are
there any glaring weaknesses?

 

[Pyrite]

I know I am practically just talking to myself now, but I have got a finished
product. I would like to know if there is a more compact way of doing it
though. To cater for multiple lines I have thrown a SUM into the party so
that it will look at the total travel hours and the total site hours for each
day, over multiple rows, without having to use a second cell.

In the 1x column I have
=SUM((D3-C3),(D4-C4),(D5-C5))+(IF(SUM((F3-E3),(F4-E4),(F5-E5))<=8,SUM((F3-E3),(F4-E4),(F5-E5)),8))

In the 1.5x column I have
=IF(SUM((F3-E3),(F4-E4),(F5-E5))>8,SUM((F3-E3),(F4-E4),(F5-E5))-8,"")


This caters for 3 rows per day but may be increased to six.

Thoughts.....

 

[Pyrite]

I have since substituted all 8's in the formula for 0.333333333333333 so that
it still works when the cells are formatted to hh:mm