Thread
:
Calculating of time
View Single Post
#
9
Posted to microsoft.public.excel.worksheet.functions
Sandy Mann
external usenet poster
Posts: 2,345
Calculating of time
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
Replace @mailinator.com with @tiscali.co.uk
"H. Nissen" wrote in message
...
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:
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
Replace @mailinator.com with @tiscali.co.uk
"H. Nissen" wrote in message
...
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:
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
Replace @mailinator.com with @tiscali.co.uk
"H. Nissen" wrote in message
...
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:
I don't understand why it is 12 | 4 | 7?
--
__________________________________
HTH
Bob
"H. Nissen" wrote in message
...
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:
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
Reply With Quote
Sandy Mann
View Public Profile
Find all posts by Sandy Mann