Calculating tiome between 17 PM and 6 AM

M

Mais qui est Paul ?

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.

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

HTH
 
S

Sandy Mann

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
 
J

Jan Kronsell

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.
 
S

Sandy Mann

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


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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top