Calculating tiome between 17 PM and 6 AM

  • Thread starter Thread starter Jan Kronsell
  • Start date Start date
Bonsour® Jan Kronsell avec ferveur ;o))) vous nous disiez :
I use the following formularto calculate how much time between start
time and end time lies between 5PM and 6AM.
=(B1<=A1)*(1-(C2)+(C1))+MIN((C1),B1)-MIN((C1),A1)+MAX((C2),B1)-MAX((C2),A1)
A1 is start time, B1 is End time, C1 is Early time limit (6 AM) and
C2 is late time limit (5 PM).
The formula does the job if i just enter fx 03:00 AM in A1 and 7:00 PM
Here is my challenge: I need to add the date to the Entry, so that I
enter
09-01-08 03:00 PM and 09-01-08 7:00 PM.
Click to expand...

=1-MAX(MOD(A1;1);LateTimeLimite)+MIN(MOD(B1;1);EarlyTimelimite)

HTH
 
Hi Herbert,

Yes, I forgot to mention that my formula only works for a two-day period -
today and tomorrow. I thought that I was being a hard task master making
poor Jan work for 55 hours in Test 7 but I see that you are making Jan work
for 62 straight hours in your last test! Shame on you. <g>

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Hi everyone

Thank you very much for your efforts. I have not yet have the opportunity to
try the latest suggestions in all combinations, but I think i will have to
make one of them do, as this problems begins to take a little too much of my
time.

Thank you again for helping out.
 
Jan Kronsell said:
Thank you very much for your efforts. I have not yet have the opportunity
to try the latest suggestions in all combinations, but I think i will have
to make one of them do, as this problems begins to take a little too much
of my time.

Thank you again for helping out.

Jan
Click to expand...


For the archives, (because the link posted will stop working after 30 days
with no downloads), with:

A2= Start time (ie called in start time)
B2= End time
C1= Regular Start time
C2= Regular end time

my final suggested formula was:

=MAX($C$2-MOD(A2,1),0)+IF(AND(INT(A2)=INT(B2),MOD(B2,1)>$C$3),MOD(B2,1)-$C$3,0)+IF(INT(A2)<>INT(B2),1-MAX($C$3,MOD(A2,1))+MIN($C$2,MOD(B2,1))+IF(MOD(B2,1)>$C$3,MOD(B2,1)-$C$3))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top