Time-problem

  • Thread starter Thread starter Ola Sigurdh
  • Start date Start date
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
 
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)
 
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
 
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)
 
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
 
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)
 
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
 
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)
 
Back
Top