Formula to calculate time

[asad]

Hi Guys,

I am a bit confused as to how can I calculate time in the following cases. I need to calculate time worked before 5:00 and after 19:00 for the given shift start and end times along with the meal break times.

Start	Meal Start	Meal End	Meal Start	Meal End	Finish	Expected Answer
03:57	08:34	09:26	11:04	11:56	13:44	01:03
13:46	15:34	16:26	19:04	19:50	22:59	03:13

<colgroup><col span="6"><col></colgroup><tbody>
</tbody>

Thanks for your help.

Asad

 

[TonyUK72]

=timevalue("05:00")-min(a1:f11)

=max(a1:f11)-timevalue("19:00")

 

[asad]

=timevalue("05:00")-min(a1:f11)

=max(a1:f11)-timevalue("19:00")

It does not consider meal breaks and gives wrong answer.
But thanks for trying though.

 

[TonyUK72]

05:00 minus 03:57 = 01:03 which is your expected answer as per you example, what do you expect the answer to be?

 

[asad]

05:00 minus 03:57 = 01:03 which is your expected answer as per you example, what do you expect the answer to be?

That one works if there are no meal breaks within that time. Look at the second example. How much comes to using your formula?

 

[TonyUK72]

2nd example

=MAX(A1:F1)-TIMEVALUE("19:00")-IF(D1>TIMEVALUE("19:00"),E1-D1,0)

 

[asad]

Thanks TonyUK72

 

[asad]

I forgot about my query but today I was working on it and worked out the formula that fits the criteria and gives correct answer. If someone wants it, here's how I did it.
I used two cells - I1 and J1 to enter the values 5:00 and 19:00.
Then I used this formula

=($I$1-MIN(A2,$I$1))+(F2-MIN(F2,$J$1))-(MAX($J$1,E2)-$J$1)+(MAX($J$1,D2)-$J$1)

It has worked for me. If someone can give me a better formula, please do so. I am always open to suggestions to learn more.