Calculating of time

H. Nissen · Jul 1, 2008

Hi

I need to make a sheet, in which i can calculate different ours.

The groundschedule is made with (t):mm and lokes like this:

=IF(G3=$D$3;0;G3)

ex: 15:00 - 05:00 = 14

But I need to calculate how many ours there are ex. from 18:00 to 22:00 and
22:00 to 06:00.

like this:

ex: 15:00 - 05:00 = 12 | 4 | 7
or
ex: 19:00 - 03:00 = 8 | 3 | 5

I hope you understand what i am trying to ask about, and I would be very
happy if someone can help me with this litle problems.

Kinds
H. Nissen

H. Nissen · Jul 1, 2008

Sorry, it was a vrong forms i put in in my first post.

It is not =IF(G3=$D$3;0;G3) but: =SUM(($D$3-E3)+F3) where $D$3 = 24

"H. Nissen" skrev:

Bob Phillips · Jul 1, 2008

I don't understand why it is 12 | 4 | 7?

H. Nissen · Jul 1, 2008

Hi Bob

It was a failure, it should be 14 | 4 | 7

Total ours = 14, ours between 18:00 and 22:00 = 4 and ours between 22:00 and
05:00 = 7

"Bob Phillips" skrev:

Sandy Mann · Jul 1, 2008

Try this out:

D3: Start time:- 18:00
E3: End Time:- 05:00
F3: Total hours:- =MOD(E3-D3,1)
G3: Hours between 18:00 and 22:00:-
=IF(AND(D3<TIME(22,,),E3<D3),MOD("22:00"-D3,1),0)
H3: Hours between 22:00 and 05:00:-
=IF(AND(D3<E3,MEDIAN(D3,E3,0)=D3),MOD(E3-D3,1),MOD(E3-MAX(D3,TIME(22,,)),1))

If you want the times returned as the numbers 11, 4 & 7 use:

F3: =MOD(E3-D3,1)*24
G3: =IF(AND(D3<TIME(22,,),E3<D3),MOD("22:00"-D3,1),0)*24
H3:
=IF(AND(D3<E3,MEDIAN(D3,E3,0)=D3),MOD(E3-D3,1),MOD(E3-MAX(D3,TIME(22,,)),1))*24

and format the cells ar General or number

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

H. Nissen · Jul 2, 2008

Hi Sandy Mann

Thx for your answer, it was very helpfull, but there is a small problems
yet. The function counts all hours before 22 o'clock and all hours after.

It must only count the hours between 18:00 and 22:00 and again the hours
between 22:00 and 5:00.

Ex: from 16:00 to 8:00 there are 16 hours, and 4 hours between 18:00 to
22:00 and again 7 hours between 22:00 to 8:00.

The function you kindly show to me, count all hours before 22:00 = 6 and
after 22:00 = 10.

So please, if you have a suggestion to solved this, I would be very happy to
hear about it

Kind regards

"Sandy Mann" skrev:

Sandy Mann · Jul 2, 2008

Replace the 18:00 - 22:00 formula with:

=IF(AND(D3<TIME(22,,),E3<D3),MOD(TIME(22,,)-MAX(TIME(18,,),D3),1),0)

Replace the 22:00 - 05:00 formula with:

=IF(AND(D3<E3,MEDIAN(D3,E3,0)=D3),MOD(MIN(TIME(5,,),E3)-D3,1),MOD(MIN(TIME(5,,),E3)-MAX(D3,TIME(22,,)),1))

Sorry for misunderstanding your requirements.

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

H. Nissen · Jul 2, 2008

Hi again Sandy Mann

Formula 18:00 to 22:00 working perfekt, thx very much, it was a great help.

The other formula 22:00 to 05:00 did not work. Excell tells me, that there
are to few arguments to this function. I had write it in a danish lang
excell, so it seems like this:

=HVIS(OG(D3<E3;MEDIAN(D5;E3;0)=D3);REST(MIN(TID(5;

;E3)-D3;1);REST(MIN(TID(5;

;E3-MAKS(D3;TID(22;

);1))

OG = And
REST = MOD
TID = TIME
MAKS = MAX.

I cant find any missing parameters, but there must be something I dont write
correct. Can you see what is may be ?

Kinds Regards

"Sandy Mann" skrev:

Sandy Mann · Jul 2, 2008

The only thing that I can think of is the implied zeros in the TID()
functions. Try:

=HVIS(OG(D3<E3;MEDIAN(D5;E3;0)=D3);REST(MIN(TID(5;0;0);E3)-D3;1);REST(MIN(TID(5;0;0);E3-MAKS(D3;TID(22;0;0));1))

Other than that I can't see what else could be wrong but if that does not
work then post back nevertheless, some of the clever people around here may
be able to suggest something else.

--
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 · Jul 2, 2008

No It can't be implied zeros because the other formula, which works, has
them. It is a missing parenthesis try:

=HVIS(OG(D3<E3;MEDIAN(D5;E3;0)=D3);REST(MIN(TID(5;

;E3)-D3;1);REST(MIN(TID(5;

;E3)-MAKS(D3;TID(22;

);1))

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

H. Nissen · Jul 2, 2008

Hi Sandy

I found out, that there was a missing ).

=HVIS(OG(D3<E3;MEDIAN(D3;E3;0)=D3);REST(MIN(TID(5;0;0);E3)-D3;1);REST(MIN(TID(5;0;0);E3-MAKS(D3;TID(22;0;0)));1))

But, the formular still counts all hours after 22:00. Not only the hours
between 22:00 and 5:00.

But again, thx for your kindly help.

Kinds regard

"Sandy Mann" skrev:

Sandy Mann · Jul 2, 2008

With 16:00 in D3 and 8:00 in E3 I get:

"Total" 16:00
"18:00 - 22:00" 04:00
"22:00 - 05:00" 07:00

What do you get returned?

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

H. Nissen · Jul 3, 2008

Hi Sandy Mann

With the same parameters,

16:00 in D3 and 8:00 in E3 I get this results:

"Total" 16:00

"18:00 - 22:00" 04:00
"22:00 - 05:00" 10:00

It is real strange, that I did not get the same result as you. My formula is
this:

=HVIS(OG(D3<E3;MEDIAN(D3;E3;0)=D3);REST(MIN(TID(5;0;0);E3)-D3;1);REST(MIN(TID(5;0;0);E3-MAKS(D3;TID(22;0;0)));1))

Kinds

H. Nissen

"Sandy Mann" skrev:

H. Nissen · Jul 3, 2008

Hi Again

Sorry, IT WORKS

I had put in the ) a vrong place, so now it works perfect. Thx for your
patience and very great help

Kinds regards
H. Nissen

"H. Nissen" skrev:

Sandy Mann · Jul 3, 2008

In the formula you posted you are still missing the parenthsis after the E3
in the second MIN() function but you have added an extra one after the
TID(22;0;0))
=HVIS(OG(D3<E3;MEDIAN(D5;E3;0)=D3);REST(MIN(TID(5;0;0);E3)-D3;1);REST(MIN(TID(5;0;0);E3)-MAKS(D3;TID(22;0;0));1))

When you said that you had found that you had found that there was a
parenthsis missing I did not take the trouble to ensure that you had placed
it in the right place. My apologies, Try:

=HVIS(OG(D3<E3;MEDIAN(D5;E3;0)=D3);REST(MIN(TID(5;0;0);E3)-D3;1);REST(MIN(TID(5;0;0);E3)-MAKS(D3;TID(22;0;0));1))

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

H. Nissen · Jul 4, 2008

Hi again

Hello again

Unfortunately, there is a bug in the formulas that make a value less than
0:00 (t.ex. 23:30) is that time calculations is not done properly.

Is there any possibility to do this?

Sincerely,

H. Nissen

D3 E3 F3 G3 H3
17:00 23:30 6:30 0:00 12:00

F3 Count total time
G3 Count between 18:00 and 22:00
H3 Count between 22:00 and 05:00

"Sandy Mann" skrev:

Sandy Mann · Jul 4, 2008

Hi,

Assuming that both D3 and E3 *can* have virually anytime of day we will need
to include the date so that we can correctly identify the number of hours.
So, with both D3 & E3 with:

D3: 04/07/2008 17:00:00
E3: 04/07/2008 23:30:00

(My Date system)

then in G3 try:

=HVIS(OG(HELTAL(D3)=HELTAL(E3);REST(E3;1)>=TID(22;

);MIN(REST(E3;1);TID(22;

)-MAKS(REST(D3;1);TID(18;

);HVIS(OG(HELTAL(E3)>HELTAL(D3);REST(D3;1)<TID(22;

);TID(22;

-MAKS(REST(D3;1);TID(18;

);0))

and in H3:

=HVIS(OG(HELTAL(D3)=HELTAL(E3);REST(E3;1)>TID(22;

);REST(E3;1)-MAKS(REST(D3;1);TID(22;

);REST((HELTAL(E3)+MIN(REST(E3;1);TID(5;

))-(HELTAL(D3)+MAKS(REST(D3;1);TID(22;

));1))

In case my translation is wrong my formulas are:

In G3:
=IF(AND(INT(D3)=INT(E3),MOD(E3,1)>=TIME(22,,)),MAX(MIN(MOD(E3,1),TIME(22,,))-MAX(MOD(D3,1),TIME(18,,)),0),IF(AND(INT(E3)>INT(D3),MOD(D3,1)<TIME(22,,)),TIME(22,,)-MAX(MOD(D3,1),TIME(18,,)),0))

In H3:
=IF(AND(INT(D3)=INT(E3),MOD(E3,1)>TIME(22,,)),MOD(E3,1)-MAX(MOD(D3,1),TIME(22,,)),MOD((INT(E3)+MIN(MOD(E3,1),TIME(5,,)))-(INT(D3)+MAX(MOD(D3,1),TIME(22,,))),1))

With the above Dates & times and formulas

I get:

F3: 6:30
G3: 4:00
H3: 1:30

And with the same in D3 and 05/07/2008 08:30:00 in E3 I get 07:00 in H3

Does this do what you want?

--
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 · Jul 5, 2008

No, my formuals below still give errors at some times. You are right to go
with Daddylonglegs formula in your other thread - that seems to work with
all time entered.

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

H. Nissen · Jul 5, 2008

hey Sandy Mann

I am very sorry, but I had not seen your response here, but I will test it,
because I can not get "18:00 - 22:00" the count to work after 24:00 crossed.

Sincerely,

H. Nissen

"Sandy Mann" skrev: