Time within time

[Charlotte E.]

Hi Guys,


I'm not sure if this can be solved with a standard workshhet function
(perferred), or if some kind of UDF is needed?

In two cells, I have a starting time in one cell, and an end time in
another cell.

I'm using 24-hours clocks.

Lets say I enter starting time 20:00, and end time 04:00 the next day.

Now I would like to know (in 3 other cells):

- How many hours are within these clock intervals:

21-23
23-00
00-03

With the given start and end time (20:00 and 04:00), it is fairly easy,
but problems occur (for me anyway , when starting and end time is
within the intervals, i.e.

Start 21:17
End 02:35

How to calculate how much time (hours and minutes) is within each of
these 3 intervals???

Thank you in advance,

CE

 

[Claus Busch]

Hi Charlotte,

Am Sat, 26 Jan 2013 11:10:01 +0100 schrieb Charlotte E.:

- How many hours are within these clock intervals:

21-23
23-00
00-03

With the given start and end time (20:00 and 04:00), it is fairly easy,
but problems occur (for me anyway , when starting and end time is
within the intervals, i.e.

Start 21:17
End 02:35

your start time in A1, end time in B1.
For case 1 time between 21-23 try:
=IF(OR(B1>TIME(23,,),B1<A1),TIME(23,,)-MAX(TIME(21,,),A1),B1-MAX(TIME(21,,),A1))
modify the formula for other cases


Regards
Claus Busch

 

[Charlotte E.]

Hi Claus,


The formula seems to work only with the first criteria, ie. 21-23

But as soon the time of midnight (00:00) is involved, one way or
another, it goes wrong...

And to be honest, this was also why I asked the question in here: I have
no problem myself getting it to work with times that doesn't involved
midnight, but I can't for the love of God make it work with criterias
involving midnight, or if the start and end time is on each side of
midnight!!!

So, if anyone could help with this, it would be a BIG help...


Thank you,


CE

Den 26.01.2013 11:54, Claus Busch skrev:

 

[Claus Busch]

Hi Charlotte,

Am Sat, 26 Jan 2013 14:46:12 +0100 schrieb Charlotte E.:

But as soon the time of midnight (00:00) is involved, one way or
another, it goes wrong...

please test the formulas for case 2 and case 3. If something is wrong
with the formulas, please let me know.
Case 2 (23-00):
=IF(AND(A1<TIME(23,,),B1<A1,B1>0),TIME(1,,),IF(AND(A1<TIME(23,,),B1<TIME(23,59,59),B1>TIME(23,,)),B1-TIME(23,,),IF(AND(A1>TIME(23,,),B1>A1,B1<TIME(23,59,59)),B1-A1,IF(AND(A1>TIME(23,,),B1<A1),MOD(0-A1,1),0))))

Case 3 (00-03):
=IF(AND(B1>TIME(3,,),A1>B1),1/8,IF(AND(B1>TIME(3,,),A1<B1),TIME(3,,)-A1,IF(AND(A1<TIME(3,,),B1<TIME(3,,),B1>A1),B1-A1,IF(AND(B1<TIME(3,,),A1>B1),B1,0))))


Regards
Claus Busch

 

[Claus Busch]

Hi Charlotte,

Am Sat, 26 Jan 2013 14:46:12 +0100 schrieb Charlotte E.:

case 2 (23-00):
=MAX(,MIN(0+(TIME(23,,)>0),B1+(A1>B1))-MAX(TIME(23,,),A1))+MAX(,(MIN(0,B1+(A1>B1))-A1)*(TIME(23,,)>0))+MAX(,MIN(0+(TIME(23,,)>0),B1+0)-TIME(23,,))*(A1>B1)

case 3 (00-03):
=MAX(,MIN(TIME(3,,)+(0>TIME(3,,)),B1+(A1>B1))-MAX(0,A1))+MAX(,(MIN(TIME(3,,),B1+(A1>B1))-A1)*(0>TIME(3,,)))+MAX(,MIN(TIME(3,,)+(0>TIME(3,,)),B1+0)-0)*(A1>B1)



Regards
Claus Busch

 

[joeu2004]

In two cells, I have a starting time in one cell,
and an end time in another cell. [....]
Now I would like to know (in 3 other cells):
- How many hours are within these clock intervals:
21-23
23-00
00-03 [....]
How to calculate how much time (hours and minutes)
is within each of these 3 intervals???

If you want the elapsed time displayed in the form h:mm, then if you start
time is in A2 and your end time is in B2:

00:00 to 03:00:
=MAX(0,MIN(B2+(B2<=A2),TIME(3,0,0))-A2)
+MAX(0,MIN(B2+(B2<=A2),1+TIME(3,0,0))-1)

03:00 to 21:00 (not requested):
=MAX(0,MIN(B2+(B2<=A2),TIME(21,0,0))-MAX(A2,TIME(3,0,0)))
+MAX(0,MIN(B2+(B2<=A2),1+TIME(21,0,0))-(1+TIME(3,0,0)))

21:00 to 23:00:
=MAX(0,MIN(B2+(B2<=A2),TIME(23,0,0))-MAX(A2,TIME(21,0,0)))
+MAX(0,MIN(B2+(B2<=A2),1+TIME(23,0,0))-(1+TIME(21,0,0)))

23:00 to 00:00:
=MAX(0,MIN(B2+(B2<=A2),1)-MAX(A2,TIME(23,0,0)))
+MAX(0,B2+(B2<=A2)-(1+TIME(23,0,0)))

Format each cell as Custom h:mm.

If you want the elapsed time displayed as decimal hours (e.g. 2.75 instead
of 2:45), multiply by 24. For example:

=24*(MAX(0,MIN(B2+(B2<=A2),TIME(3,0,0))-A2)
+MAX(0,MIN(B2+(B2<=A2),1+TIME(3,0,0))-1))

or

=24*MAX(0,MIN(B2+(B2<=A2),TIME(3,0,0))-A2)
+24*MAX(0,MIN(B2+(B2<=A2),1+TIME(3,0,0))-1)

In general, it is the amount of time between 00:00 and 03:00 (e.g.) in the
start day plus the amount of time between 00:00 and 03:00 in the next day.

The actual end time is that time of day in the next day if end time is less
than or equal to start time. Thus, we add 1 to the end time.

The "time" 1 represents 00:00 in the day after the start day.

 

[joeu2004]

Clarification....

Now I would like to know (in 3 other cells):
- How many hours are within these clock intervals:
21-23
23-00
00-03 [....]
How to calculate how much time (hours and minutes)
is within each of these 3 intervals???

It is not clear whether you mean only 21:00 to 23:00 and 23:00 to 00:00 on
the start day and 00:00 to 00:03 on the end day, or you mean the sum of time
in those intervals on each day.

I interpreted the requirement as the latter.

For example, if the start time is 22:00 and the end time is 21:59 (the next
day implicitly), I count the amount of time between 21:00 to 23:00 as
follows:

1h between 21:00 to 23:00 on the start day
plus
59m between 21:00 and 23:00 on the end day

Is that what you want?

 

[Charlotte E.]

The latter way, that's excately the way I want it

So, you got it right - and your formulas seems to be more simple than
the ones provided by Claus - I'll put them tp the test

Thanks you very much for helping me out here...


CE


Den 26.01.2013 21:06, joeu2004 skrev:

Clarification....

Now I would like to know (in 3 other cells):
- How many hours are within these clock intervals:
21-23
23-00
00-03 [....]
How to calculate how much time (hours and minutes)
is within each of these 3 intervals???

It is not clear whether you mean only 21:00 to 23:00 and 23:00 to 00:00
on the start day and 00:00 to 00:03 on the end day, or you mean the sum
of time in those intervals on each day.

I interpreted the requirement as the latter.

For example, if the start time is 22:00 and the end time is 21:59 (the
next day implicitly), I count the amount of time between 21:00 to 23:00
as follows:

1h between 21:00 to 23:00 on the start day
plus
59m between 21:00 and 23:00 on the end day

Is that what you want?

 

[Charlotte E.]

Hi Joeu,


It turned out that under some conditions the functions, I got from Claus
gave the wrong results...

So, I gave your functions a test-drive, and they worked under every
conditions

So, once again thank you for your time and effort...


CE


Den 26.01.2013 21:06, joeu2004 skrev:

Clarification....

Now I would like to know (in 3 other cells):
- How many hours are within these clock intervals:
21-23
23-00
00-03 [....]
How to calculate how much time (hours and minutes)
is within each of these 3 intervals???

It is not clear whether you mean only 21:00 to 23:00 and 23:00 to 00:00
on the start day and 00:00 to 00:03 on the end day, or you mean the sum
of time in those intervals on each day.

I interpreted the requirement as the latter.

For example, if the start time is 22:00 and the end time is 21:59 (the
next day implicitly), I count the amount of time between 21:00 to 23:00
as follows:

1h between 21:00 to 23:00 on the start day
plus
59m between 21:00 and 23:00 on the end day

Is that what you want?