Time-problem

O

Ola Sigurdh

Hello

I have a timesheet where I enter start and end times. My problem is that I
want to check if any or all of the elapsed time falls into a certain
timeframe.

Here is an example.

If I put 07:00 into D4 and 16;00 into G4 in H4 the answer is 9, Which is
correct (I use C Pearsons formula so I can calculate shifts over midnight..
Then I want to se if the timeframe specified falls into into a timeframe
between 19:00 to 06:00 and show the result in I4.

D4 G4 H4 I4
04:00 14:00 10 2
17:00 23:00 6 4
21:00 05:00 8 8

I use this to count the numbers of time someome will be paid extra for
working in unsocial working hours.
I hope you understand what I mean

TIA
Ola
 
B

Bob Phillips

Ola,

A bit clumsy, but try this

in F3: =TIME(19,0,0)
in G3: =TIME(6,0,0)
in I4:
=(D4>=$F$3)*(1-D4)+(E4<=$G$3)*(E4)+(E4>=$F$3)*(E4-$F$3)+(D4<=$G$3)*($G$3-D4)
and copy I4 down

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
O

Ola Sigurdh

Thank you for your help Bob.

There is just a problem left, if a shift starts before the time in F3 and
ends from midnight and later it ignores the time up to midnight. It just
shows the time after midnigth.

TIA

Ola
 
B

Bob Phillips

Ola,

Can you give me example times so that I can plug them into my workbook?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
O

Ola Sigurdh

Hello Bob

If I put in starttime 15:00 in cell D4 and endtime 02:00 in cell E4 I get
11 hours total workinghours but only 2 hours of unsocial time, it would be 6
hours unsocial time.
TIA

Ola
 
B

Bob Phillips

Ola,

It's OK, I worked it out. There is a similar problem if the shift extends
over midnight and finishes after 6:00am.

I am not particularly happy with this, but try this

=(D8>=$F$3)*(1-D8)+(E8<=$G$3)*(E8)+(E8>=$F$3)*(E8-$F$3)+(D8<=$G$3)*($G$3-D8)
+(AND(E8<D8,D8<$F$3)*(1-$F$3))+(AND(E8<D8,E8>$G$3)*($G$3))

I will try and improve it, but a real worksheet formula wizz will probably
give a neater solution.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
S

Sandy Mann

Bob,

I still get errors with your latest formula. For example unless I have made
mistakes when entering the formula, the times 1:00 and 2:00 return 7:00
instead of 1:00.
Harlan Grove's formula, which he posted in response to a very similar
question, ("microsoft.public.excel.worksheet.functions" Re: Far too many
"IF"s for my own good - 30 December 2003) , works. I have translated it for
the cells that you were using:

=((E4<=D4)*(1-$F$3+$G$3)+MIN($G$3,E4)-MIN($G$3,D4)+MAX($F$3,E4)-MAX($F$3,D4)
)

the OP will have to transpose the E4's for G4's which was the OP's original
end time cell.

Personally I am still trying to figure out how it works

Regards

Sandy
 
B

Bob Phillips

Sandy,

No, you are right, it is still flawed.

Harlan's solution is much better and works for all circumstances that I can
think of.

((E4<=D4)*(1-$IF$3+$G$3) - If it spans midnight, adds in the unsocial hours
+MIN($G$3,E4) - Adds in the earlier of end time and the
unsocial hours end time
-MIN($G$3,D4) - Subtrracts the earlier of start time and
the unsocial hours end time
+MAX($F$3,E4) - Adds in the later of end time and the
unsocial hours start time
-MAX($F$3,D4) - Subtracts the later of start time and the
unsocial hours start time

This was the approach I initially sought, work out the total hours and
subtract the non-unsocial hours, but I couldn't get it to work.

-

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top