Calculating tiome between 17 PM and 6 AM

[Jan Kronsell]

I thought I was quite good at date/time calculations, but apparently not
good enough.

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.



I have tried a lot of different solutions, and also read the articles on
Chip Pearsons site. I can get i to work in some situations, but not in all.



For instance



09-01-08 03:00 AM and 09-03-08 7:00 PM will miscalculate.



I need a formula, that works on all date/time combinations.



This formula calculates how much time lies between 6 PM and 17 PM



=IF(INT(B2-A2)>0,((INT(B2-A2)*C1)+C2-MAX(C1,A2-INT(A2))+MAX(B2-INT(B2),C1)-C1)-IF(B2-INT(B2)>=A2-INT(A2),C1),MIN(C2,B2-INT(B2))-MAX(A2-INT(A2),C1))



But I simply do not seem to be able to change it to do the other
calculation. The correct answer to the above date/times should total to 30
hours.



From 09-01-08 03:00 AM to 09-01-08 06:00 AM = 2 hours

From 09-01-08 05:00 PM to 09-02-08 06:00 AM = 13 hours

From 09-02-08 05:00 PM to 09-03-08 06:00 AM = 13 hours

From 09-02-08 05:00 PM to 09-03-08 07:00 PM = 2 hours



Is there any way to achieve what I need?



Jan

 

[Sandy Mann]

I can't get some of your examples to agree with your text. I read your post
as asking to calculate only the hours from 6 AM to 5 PM on each day. If
that is what you want then try:

=(INT(B1)-INT(A1))*(C2-C1)+MAX(MOD(B1,1),C1)-MIN(MAX(MOD(A1,1),C1),C2)

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

 

[Sandy Mann]

I omitted to say ensure that the cell is formatted as [h]:mm

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

 

[Jan Kronsell]

Actually it is the other way around. I want to calculate only the time
between 5PM and 6AM.
From 6AM to 5PM is normal working hours, and I like to calculate the time
spend outside normal working hours.

If I arrive at work on september 1st at 8 AM and go home September 1st at
4 PM 0 hours is outside normal working hours.
If I arrive at work on september 1st at 11 PM and go home September 2st at
7 AM 7 hours is outside normal working hours.
If I arrive at work on september 1st at 4 AM and go home September 1st at
6 PM 3 hours is outside normal working hours.
If I arrive at work on September 1st at 4 AM and go home at September 2nd
at 6PM, 16 hours is outside normal working hours.
If I arrive at work on September 1st at 4 AM and go home at September 3rd
at 6PM, 29 hours is outside normal working hours.


I can get different formulas io work, in some cases, but not in others and
I'm looking for a formula that works in all cases, no matter when I arrive
or leave.

Jan

 

[Sandy Mann]

Well try subtracting my original formual, (with a slight change), from the
total time:

=(B1-A1)-((INT(B1)-INT(A1))*(C2-C1)+MAX(MIN(MOD(B1,1),C2),C1)-MIN(MAX(MOD(A1,1),C1),C2))

I haven't extesively tested it but it meets all the conditions you gave
except the original:


Which my fingers make: 3 hours, 14 hours, 14 hours & 14 hours respectively
(or 1 hour is you meant 09-02-08 to 09-02-08)



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

 

[Herbert Seidenberg]

Sandy,
If we assume one typo,
the examples would make more sense:
http://www.savefile.com/files/1771243

 

[Sandy Mann]

Sandy,
If we assume one typo,
the examples would make more sense:
http://www.savefile.com/files/1771243

Hi Herbert,

We agree on the typo in the first one and the 13:00 hours in the next two -
which my formula does return this morning, must have been too late for me
last night - thus the resorting to fingers <g> - but surely from 09/02/2008
17:00:00 to 09/03/2008 19:00:00 cannot be 2:00 hours? 17:00 to 19:00 on
09/03/2008 is 2:00 on its own without the rest of the time.

Your method of working it out is very interesting, I have to take my wife to
see her brother who has broken his leg but I will certainly analyse it when
I come home

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

 

[Jan Kronsell]

I tried your formula but cannot get it to work correctly.

From 09-01-08 03:00 AM to 09-01-08 06:00 PM = 3 hours.(The formula returns
15 hours).
From 09-01-08 08:00 AM to 09-01-08 02:00 PM = 0 hours (The formula returns 6
hours).

This looks like something I acchieved myself - a formula, that works on
sonme data/time combinations, but not all .

Jan

 

[Jan Kronsell]

This seems to work - even if I quite can't understand, what you are doing


Jan

 

[Craig Schiller]

I don't understand. The results are correct for the two examples you cited.

 

[Herbert Seidenberg]

Added bells and whistles.
Same link.

 

[Jan Kronsell]

I don't understand. The results are correct for the two examples you
cited.

When I try its not. As I wrote, the first eaxample should return 3 hours (2
between 3and 5 am and 1 between 5 and 6 pm. But it returns 15.
In the second example the formula returns 6 hours, but it should return 0.

Jan

 

[Jan Kronsell]

I will look at it again tomorrow:

Jan

 

[Sandy Mann]

Herbert,

I am baffled as to why both you and the Jan are convinced that:

9/02/2008 5:00 PM to 9/03/2008 7:00 PM is 2 hours?


Note that the dates are not the same. 5:00 PM to Midnight is 7 hours,
Midnight to 6:00 AM is 6 hours and 5:00 PM to 7:00 PM is two hours making a
total of 15 hours. Or am I missing something?


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

 

[Sandy Mann]

Jan,

You are right that my original formulas returned error for some
times/dates. If you are happy with Herbert's formula then that is fine by
me but I find that it also returns error for some times/dates. The formula
in this link is not very elegant but seems to return the correct hours for
the testing that I have done:

http://www.savefile.com/files/1772665

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

 

[Herbert Seidenberg]

Sandy:
My original algorithm for the first set of
examples used only the one Set
that was associated with the End.
This achieved the maximum number of matches
to Jan's results.
No common sense, no fingers, just math.
The second set of examples made the original
algorithm untenable.
So here is another try that matches 7 out of Jan's 9 results.
Same link.

 

[Jan Kronsell]

It does. But that was not, what i wanted I need only the hours beween
5PM and 6AM

Jan

"Craig Schiller" <[email protected]> skrev i en meddelelse
I must have missed your original premise, because the formulas are returning
correct results for straight subtraction of the two times.

Jan Kronsell wrote:

Craig Schiller wrote:

I don't understand. The results are correct for the two examples you
cited.


When I try its not. As I wrote, the first eaxample should return 3 hours (2
between 3and 5 am and 1 between 5 and 6 pm. But it returns 15.
In the second example the formula returns 6 hours, but it should return 0.

Jan


Jan Kronsell wrote:


I tried your formula but cannot get it to work correctly.

From 09-01-08 03:00 AM to 09-01-08 06:00 PM = 3 hours.(The formula
returns 15 hours).
From 09-01-08 08:00 AM to 09-01-08 02:00 PM = 0 hours (The formula
returns 6 hours).

This looks like something I acchieved myself - a formula, that works
on sonme data/time combinations, but not all .

Jan

 

[Jan Kronsell]

I agreen completely. The the times you specify is 15 hours, not 2 hours.

Jan

 

[Jan Kronsell]

Jan,

You are right that my original formulas returned error for some
times/dates. If you are happy with Herbert's formula then that is
fine by me but I find that it also returns error for some
times/dates. The formula in this link is not very elegant but seems
to return the correct hours for the testing that I have done:

http://www.savefile.com/files/1772665

 

[Jan Kronsell]

I try it later today.

Apparently this exact calculations is not so easy as I thought. For sone
reason most formulas can handle some situations but not all. So I really
hope, that yourswill work in general.

Jan