Time calculation "challenge"

I have run into a time calculation problem, that is more challenging, that I
tought it would be.

I have looked at Chip Pearsons site, but I have not been able to find
anything solving my specific problem, but maybe I haven't lookee good
enough.

Anyway, here goes:

in a spreadsheet I enter a StartTime ( in A1) and end EndTime ( in a B1). In
C1 i calculate the time between A1 and B1. That part works perfectly OK. Now
themy challenge is to calculate, how much time between StartTime and EndTime
lies with the interval between 6AM (06:00) and 5PM (17:00).

I tried with different formulas, and I can get each of them to work on
certain StartTimes and EndTimes, but not on other. I have trouble finding a
formula, that covers all StartTime/EndTime scenarios.

StartTime can be anything between 00:00 and 23:59. The Same goes for
EndTime.

Here are the differenct scenarios, I have:

1) StartTime after 6AM, Endtime Before 5PM. This formula does the job:
=IF(AND(A1>=(6/24),B1<=(17/24)),B1-A1)



2) StartTime before 5PM, Endtime After 5PM. This formula does the job:

=IF((17/24)-A1<0,0,(17/24)-A1)



3) StartTime after 5PM and before midnight, Endtime After 5PM. The formula
from 2) does the job.



4) StartTime after midnight, EndTime before 6AM. The formula from 2) and 3)
does not work, this one does: =IF(AND(A1>=0,A1<=(6/24)),(B1-A1))



5) StartTime after 5PM, EndTime after 6AM



6) StartTime before 5PM, EndTime after 6AM



I haven't been able how to calculate scenario 5 or 6, and I have no clue how
to put it all together in one single formula, that handles all the
scenarios.



Can anybody help?



Jan

Maybe I shold add, that if StartTime is 16:00 (4PM) and EndTime is 07:00AM
the number I need should be 2:00.

That is, the time within the 6:00-17:00 interval on both sides of the
interval 17:00-6:00.

Jan

"Jan Kronsell" <kronsell(removebeforesend)@adslhome.dk> skrev i en
meddelelse news:[email protected]...
> I have run into a time calculation problem, that is more challenging, that
I
> tought it would be.
>
> I have looked at Chip Pearsons site, but I have not been able to find
> anything solving my specific problem, but maybe I haven't lookee good
> enough.
>
> Anyway, here goes:
>
> in a spreadsheet I enter a StartTime ( in A1) and end EndTime ( in a B1).
In
> C1 i calculate the time between A1 and B1. That part works perfectly OK.
Now
> themy challenge is to calculate, how much time between StartTime and
EndTime
> lies with the interval between 6AM (06:00) and 5PM (17:00).
>
> I tried with different formulas, and I can get each of them to work on
> certain StartTimes and EndTimes, but not on other. I have trouble finding
a
> formula, that covers all StartTime/EndTime scenarios.
>
> StartTime can be anything between 00:00 and 23:59. The Same goes for
> EndTime.
>
> Here are the differenct scenarios, I have:
>
> 1) StartTime after 6AM, Endtime Before 5PM. This formula does the job:
> =IF(AND(A1>=(6/24),B1<=(17/24)),B1-A1)
>
>
>
> 2) StartTime before 5PM, Endtime After 5PM. This formula does the job:
>
> =IF((17/24)-A1<0,0,(17/24)-A1)
>
>
>
> 3) StartTime after 5PM and before midnight, Endtime After 5PM. The formula
> from 2) does the job.
>
>
>
> 4) StartTime after midnight, EndTime before 6AM. The formula from 2) and
3)
> does not work, this one does: =IF(AND(A1>=0,A1<=(6/24)),(B1-A1))
>
>
>
> 5) StartTime after 5PM, EndTime after 6AM
>
>
>
> 6) StartTime before 5PM, EndTime after 6AM
>
>
>
> I haven't been able how to calculate scenario 5 or 6, and I have no clue
how
> to put it all together in one single formula, that handles all the
> scenarios.
>
>
>
> Can anybody help?
>
>

Hi Jan,

one way with start time in A1, end time in B1, time span you want to check
in A2 (06:00) and B2 (17:00)


=MOD(B1-A1,1)-(MAX(0,MIN(B1,A2)-IF(B1>A1,A1,MIN(0,A1-A2)))+MAX(0,1-MAX(B2,A1)-IF(B1>A1,1-B1,MIN(0,B2-B1))))

probably unnecessary big formula but will work



in Danish

=REST(B1-A1;1)-(MAKS(0;MIN(B1;A2)-HVIS(B1>A1;A1;MIN(0;A1-A2)))+MAKS(0;1-MAKS(B2;A1)-HVIS(B1>A1;1-B1;MIN(0;B2-B1))))

--


Regards,

Peo Sjoblom


"Jan Kronsell" <kronsell(removebeforesend)@adslhome.dk> wrote in message
news:[email protected]...
> Maybe I shold add, that if StartTime is 16:00 (4PM) and EndTime is 07:00AM
> the number I need should be 2:00.
>
> That is, the time within the 6:00-17:00 interval on both sides of the
> interval 17:00-6:00.
>
> Jan
>
> "Jan Kronsell" <kronsell(removebeforesend)@adslhome.dk> skrev i en
> meddelelse news:[email protected]...
>> I have run into a time calculation problem, that is more challenging,
>> that
> I
>> tought it would be.
>>
>> I have looked at Chip Pearsons site, but I have not been able to find
>> anything solving my specific problem, but maybe I haven't lookee good
>> enough.
>>
>> Anyway, here goes:
>>
>> in a spreadsheet I enter a StartTime ( in A1) and end EndTime ( in a B1).
> In
>> C1 i calculate the time between A1 and B1. That part works perfectly OK.
> Now
>> themy challenge is to calculate, how much time between StartTime and
> EndTime
>> lies with the interval between 6AM (06:00) and 5PM (17:00).
>>
>> I tried with different formulas, and I can get each of them to work on
>> certain StartTimes and EndTimes, but not on other. I have trouble finding
> a
>> formula, that covers all StartTime/EndTime scenarios.
>>
>> StartTime can be anything between 00:00 and 23:59. The Same goes for
>> EndTime.
>>
>> Here are the differenct scenarios, I have:
>>
>> 1) StartTime after 6AM, Endtime Before 5PM. This formula does the job:
>> =IF(AND(A1>=(6/24),B1<=(17/24)),B1-A1)
>>
>>
>>
>> 2) StartTime before 5PM, Endtime After 5PM. This formula does the job:
>>
>> =IF((17/24)-A1<0,0,(17/24)-A1)
>>
>>
>>
>> 3) StartTime after 5PM and before midnight, Endtime After 5PM. The
>> formula
>> from 2) does the job.
>>
>>
>>
>> 4) StartTime after midnight, EndTime before 6AM. The formula from 2) and
> 3)
>> does not work, this one does: =IF(AND(A1>=0,A1<=(6/24)),(B1-A1))
>>
>>
>>
>> 5) StartTime after 5PM, EndTime after 6AM
>>
>>
>>
>> 6) StartTime before 5PM, EndTime after 6AM
>>
>>
>>
>> I haven't been able how to calculate scenario 5 or 6, and I have no clue
> how
>> to put it all together in one single formula, that handles all the
>> scenarios.
>>
>>
>>
>> Can anybody help?
>>
>>
>
>
>

I will try it out :-) then getr back to you.

Jan
"Peo Sjoblom" <[email protected]> skrev i en meddelelse
news:evRBD#[email protected]...
> Hi Jan,
>
> one way with start time in A1, end time in B1, time span you want to check
> in A2 (06:00) and B2 (17:00)
>
>
>
=MOD(B1-A1,1)-(MAX(0,MIN(B1,A2)-IF(B1>A1,A1,MIN(0,A1-A2)))+MAX(0,1-MAX(B2,A1
)-IF(B1>A1,1-B1,MIN(0,B2-B1))))
>
> probably unnecessary big formula but will work
>
>
>
> in Danish
>
>
=REST(B1-A1;1)-(MAKS(0;MIN(B1;A2)-HVIS(B1>A1;A1;MIN(0;A1-A2)))+MAKS(0;1-MAKS
(B2;A1)-HVIS(B1>A1;1-B1;MIN(0;B2-B1))))
>
> --
>
>
> Regards,
>
> Peo Sjoblom
>
>
> "Jan Kronsell" <kronsell(removebeforesend)@adslhome.dk> wrote in message
> news:[email protected]...
> > Maybe I shold add, that if StartTime is 16:00 (4PM) and EndTime is
07:00AM
> > the number I need should be 2:00.
> >
> > That is, the time within the 6:00-17:00 interval on both sides of the
> > interval 17:00-6:00.
> >
> > Jan
> >
> > "Jan Kronsell" <kronsell(removebeforesend)@adslhome.dk> skrev i en
> > meddelelse news:[email protected]...
> >> I have run into a time calculation problem, that is more challenging,
> >> that
> > I
> >> tought it would be.
> >>
> >> I have looked at Chip Pearsons site, but I have not been able to find
> >> anything solving my specific problem, but maybe I haven't lookee good
> >> enough.
> >>
> >> Anyway, here goes:
> >>
> >> in a spreadsheet I enter a StartTime ( in A1) and end EndTime ( in a
B1).
> > In
> >> C1 i calculate the time between A1 and B1. That part works perfectly
OK.
> > Now
> >> themy challenge is to calculate, how much time between StartTime and
> > EndTime
> >> lies with the interval between 6AM (06:00) and 5PM (17:00).
> >>
> >> I tried with different formulas, and I can get each of them to work on
> >> certain StartTimes and EndTimes, but not on other. I have trouble
finding
> > a
> >> formula, that covers all StartTime/EndTime scenarios.
> >>
> >> StartTime can be anything between 00:00 and 23:59. The Same goes for
> >> EndTime.
> >>
> >> Here are the differenct scenarios, I have:
> >>
> >> 1) StartTime after 6AM, Endtime Before 5PM. This formula does the job:
> >> =IF(AND(A1>=(6/24),B1<=(17/24)),B1-A1)
> >>
> >>
> >>
> >> 2) StartTime before 5PM, Endtime After 5PM. This formula does the job:
> >>
> >> =IF((17/24)-A1<0,0,(17/24)-A1)
> >>
> >>
> >>
> >> 3) StartTime after 5PM and before midnight, Endtime After 5PM. The
> >> formula
> >> from 2) does the job.
> >>
> >>
> >>
> >> 4) StartTime after midnight, EndTime before 6AM. The formula from 2)
and
> > 3)
> >> does not work, this one does: =IF(AND(A1>=0,A1<=(6/24)),(B1-A1))
> >>
> >>
> >>
> >> 5) StartTime after 5PM, EndTime after 6AM
> >>
> >>
> >>
> >> 6) StartTime before 5PM, EndTime after 6AM
> >>
> >>
> >>
> >> I haven't been able how to calculate scenario 5 or 6, and I have no
clue
> > how
> >> to put it all together in one single formula, that handles all the
> >> scenarios.
> >>
> >>
> >>
> >> Can anybody help?
> >>
> >>
> >
> >
> >
>
>

Hi Peo

It workes perfectly allright. Can you explain the formula to me. I like to
understand, what Im doing.

Jan

"Peo Sjoblom" <[email protected]> skrev i en meddelelse
news:evRBD#[email protected]...
> Hi Jan,
>
> one way with start time in A1, end time in B1, time span you want to check
> in A2 (06:00) and B2 (17:00)
>
>
>
=MOD(B1-A1,1)-(MAX(0,MIN(B1,A2)-IF(B1>A1,A1,MIN(0,A1-A2)))+MAX(0,1-MAX(B2,A1
)-IF(B1>A1,1-B1,MIN(0,B2-B1))))
>
> probably unnecessary big formula but will work
>
>
>
> in Danish
>
>
=REST(B1-A1;1)-(MAKS(0;MIN(B1;A2)-HVIS(B1>A1;A1;MIN(0;A1-A2)))+MAKS(0;1-MAKS
(B2;A1)-HVIS(B1>A1;1-B1;MIN(0;B2-B1))))
>
> --
>
>
> Regards,
>
> Peo Sjoblom
>
>
> "Jan Kronsell" <kronsell(removebeforesend)@adslhome.dk> wrote in message
> news:[email protected]...
> > Maybe I shold add, that if StartTime is 16:00 (4PM) and EndTime is
07:00AM
> > the number I need should be 2:00.
> >
> > That is, the time within the 6:00-17:00 interval on both sides of the
> > interval 17:00-6:00.
> >
> > Jan
> >
> > "Jan Kronsell" <kronsell(removebeforesend)@adslhome.dk> skrev i en
> > meddelelse news:[email protected]...
> >> I have run into a time calculation problem, that is more challenging,
> >> that
> > I
> >> tought it would be.
> >>
> >> I have looked at Chip Pearsons site, but I have not been able to find
> >> anything solving my specific problem, but maybe I haven't lookee good
> >> enough.
> >>
> >> Anyway, here goes:
> >>
> >> in a spreadsheet I enter a StartTime ( in A1) and end EndTime ( in a
B1).
> > In
> >> C1 i calculate the time between A1 and B1. That part works perfectly
OK.
> > Now
> >> themy challenge is to calculate, how much time between StartTime and
> > EndTime
> >> lies with the interval between 6AM (06:00) and 5PM (17:00).
> >>
> >> I tried with different formulas, and I can get each of them to work on
> >> certain StartTimes and EndTimes, but not on other. I have trouble
finding
> > a
> >> formula, that covers all StartTime/EndTime scenarios.
> >>
> >> StartTime can be anything between 00:00 and 23:59. The Same goes for
> >> EndTime.
> >>
> >> Here are the differenct scenarios, I have:
> >>
> >> 1) StartTime after 6AM, Endtime Before 5PM. This formula does the job:
> >> =IF(AND(A1>=(6/24),B1<=(17/24)),B1-A1)
> >>
> >>
> >>
> >> 2) StartTime before 5PM, Endtime After 5PM. This formula does the job:
> >>
> >> =IF((17/24)-A1<0,0,(17/24)-A1)
> >>
> >>
> >>
> >> 3) StartTime after 5PM and before midnight, Endtime After 5PM. The
> >> formula
> >> from 2) does the job.
> >>
> >>
> >>
> >> 4) StartTime after midnight, EndTime before 6AM. The formula from 2)
and
> > 3)
> >> does not work, this one does: =IF(AND(A1>=0,A1<=(6/24)),(B1-A1))
> >>
> >>
> >>
> >> 5) StartTime after 5PM, EndTime after 6AM
> >>
> >>
> >>
> >> 6) StartTime before 5PM, EndTime after 6AM
> >>
> >>
> >>
> >> I haven't been able how to calculate scenario 5 or 6, and I have no
clue
> > how
> >> to put it all together in one single formula, that handles all the
> >> scenarios.
> >>
> >>
> >>
> >> Can anybody help?
> >>
> >>
> >
> >
> >
>
>

One way to work out how this excellent formula works is to assign range names to all the 4 cells:

A1: timeon
B1: timeoff
A2: beginday
B2: endday

Then edit the formula, and double-click each cell ref, and click on the corresponding cell, you should get:

=MOD(timeoff-timeon,1)
-(MAX(0,MIN(timeoff,beginday)-IF(timeoff>timeon,timeon,MIN(0,timeon-beginday)))
+MAX(0,1-MAX(endday,timeon)-IF(timeoff>timeon,1-timeoff,MIN(0,endday-timeoff))))

I haven't finished interpreting this yet, I'll get back to it later.

Regards
Mike