Hours and minutes within/outside interval

[Jan Kronsell]

I have a spreadsheet with three columns, In colum A I have Start time in
hours and minutes and in column B I have the End time in hours and minutes.
In column C I calculate Spend time. This works perfectly OK.

Now I need a fourthand fifth column. In this I need to calculate how much of
my Spend time, lies with in the interval 06:00 to 17:00 (six AM to 5 PM).
and how much lies outside

Start time can be anywhere between 00:00 ands 23:59, and the same goes for
End time.

Examples
Start time: End time: Spend time
Within the interval Outside the interval
08:00 16:00 8:00
8:00 0:00
09:15 18:00 8:45
7:45 1:00
03:00 12:00 9:00
6:00 3:00
05:00 20:00 15:00
11:00 4:00
18:00 07:00 13:00
1:00 12:00

Any ideas how to accomplish this this?

Jan

 

[Harald Staff]

Dav Jan

One way. Start in A1, End in B1, "inside" formula in C1:
=MAX(MIN(B1+(B1<A1),17/24),6/24)-MAX(A1,6/24)
and "outside" in D1
=B1+(B1<A1)-A1-C1

HTH. Best wishes Harald

 

[Mangesh Yadav]

Just change the "inside" to:

ABS(MAX(MIN(B6+(B6<A6),17/24),6/24)-MAX(A6,6/24))

to take care of the last case.

Mangesh

 

[Jan Kronsell]

Hej Harald

Thank you. It works perfectly allright. Only fore some reason the result og
the "inside" was negative, so I added an ABS

=ABS(MAX(MIN(B1+(B1<A1),17/24),6/24)-MAX(A1,6/24))

Jan

 

[Jan Kronsell]

I was a little early in confirming it worked. Is does for most calculations
but with

Start time: 18:00 End time: 22:30

the formula calculates 1 hour "inside" interval. Why is that?

Jan

 

[Jan Kronsell]

I was a little early in confirming it worked. Is does for most calculations
but with

Start time: 18:00 End time: 22:30

the formula calculates 1 hour "inside" interval. Why is that?

Jan

I can see now, that the miscalculation happens every time both start time
and end time is after 17:00.
But it works ok if the are both before 6:00.

Jan

 

[Harald Staff]

Sorry, my fault. This should do:

=MAX(MIN(B1+(B1<A1),17/24),6/24)-MIN(MAX(A1,6/24),17/24)

Split it in two separate cells while working on it, and it's easier to
follow and test:

=MAX(MIN(B1+(B1<A1),17/24),6/24)
=MIN(MAX(A1,6/24),17/24)

Best wishes Harald

 

[Jan Kronsell]

Thanks again. That almost did the trick. Only problem now seems to be if
start time is higher that end time fx 18:00 to 17:00 gives 0 hours inside.
but it should be 11.

Jan

 

[Harald Staff]

Ouch, you're right. 16:00 to 07:00 also, it's supposed to be 2.
I'd check for both today's and next day's interval like this:

=MAX(MIN(B1+(B1<A1),17/24),6/24)-MIN(MAX(A1,6/24),17/24)+MAX(MIN(B1+(B1<A1),41/24),30/24)-MIN(MAX(A1,30/24),41/24)

Maybe there's a neater solution out there.

HTH. Best wishes Harald

 

[Jan Kronsell]

Hej Harald

Thank you very much. I try it tomorrow, when I'm a bit more awake . I
have to weave into a somewhat more complex formula. I let you know of the
result.
Jan

 

[Sandy Mann]

Ouch, you're right. 16:00 to
07:00 also, it's supposed to be 2.

I'd check for both today's and next day's interval like this:

=MAX(MIN(B1+(B1<A1),17/24),6/24)-MIN(MAX(A1,6/24),17/24)+MAX(MIN(B1+(B1<A1),
41/24),30/24)-MIN(MAX(A1,30/24),41/24)

Maybe there's a neater solution out there

I haven't been following this thread too closely but Harlan posted a formula
some time ago for calculating times outside of certain hours. I never
noticed at then time but it returns a number if one or both time entry cells
are blank so will need wrapping in an IF statement.

http://tinyurl.com/83hll

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Jan Kronsell]

And Harlans formula is a bit shorter than Haralds
I think I look a bit at that. Then get back.

Jan

 

[Jan Kronsell]

After having wowen Harlans fromula into my own, this is what I have come up
with:


=IF(ISNUMBER(IF(OR(ISBLANK(D6),ISBLANK(E6))=TRUE,"",(E6<=D6)*(1-bte+btb)+MIN
(btb,E6)-MIN(btb,D6)+MAX(bte,E6)-MAX(bte,D6))),

IF(OR(ISBLANK(D6),ISBLANK(E6))=TRUE,"",(E6<=D6)*(1-bte+btb)+MIN(btb,E6)-MIN(
btb,D6)+MAX(bte,E6)-MAX(bte,D6)),IF(OR(ISBLANK(B6),

ISBLANK(C6))=TRUE,"",(C6<=B6)*(1-bte+btb)+MIN(btb,C6)-MIN(btb,B6)+MAX(bte,C6
)-MAX(bte,B6)))



Apparently that does the trick I needed. So thank you to you both Harals and
Sandy.

Jan

 

[Sandy Mann]

You're welcome - I'm good at reflected glory <g>

--
Regards

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Harald Staff]

A bit shorter, eh ? <bg>
Anyway, I look forward to examine Harlan's approach on this. Thanks
everyone.

Best wishes Harald

 

[Jan Kronsell]

Harlans was a bit shorter. My wasn't

Jan

A bit shorter, eh ? <bg>
Anyway, I look forward to examine Harlan's approach on this. Thanks
everyone.

Best wishes Harald


=IF(ISNUMBER(IF(OR(ISBLANK(D6),ISBLANK(E6))=TRUE,"",(E6<=D6)*(1-bte+btb)+MIN
IF(OR(ISBLANK(D6),ISBLANK(E6))=TRUE,"",(E6<=D6)*(1-bte+btb)+MIN(btb,E6)-MIN(
ISBLANK(C6))=TRUE,"",(C6<=B6)*(1-bte+btb)+MIN(btb,C6)-MIN(btb,B6)+MAX(bte,C6
=MAX(MIN(B1+(B1<A1),17/24),6/24)-MIN(MAX(A1,6/24),17/24)+MAX(MIN(B1+(B1<A1),