| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating of time
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating of time
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating of time
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating of time
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating of time
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating of time
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating of time
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating of time
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 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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating of time
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: 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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating of time
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 Replace @mailinator.com with @tiscali.co.uk "H. Nissen" wrote in message ... 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: 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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating of time
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: 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 Replace @mailinator.com with @tiscali.co.uk "H. Nissen" wrote in message ... 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: 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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating of time
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: 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: 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 Replace @mailinator.com with @tiscali.co.uk "H. Nissen" wrote in message ... 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: 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 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating of time
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 Replace @mailinator.com with @tiscali.co.uk "H. Nissen" wrote in message ... 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: 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 Replace @mailinator.com with @tiscali.co.uk "H. Nissen" wrote in message ... 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: 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 |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating of time
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: 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 Replace @mailinator.com with @tiscali.co.uk "H. Nissen" wrote in message ... 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: 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 Replace @mailinator.com with @tiscali.co.uk "H. Nissen" wrote in message ... 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: 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. |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating of time
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)HELT AL(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(RE ST(E3;1);TID(5;;)))-(HELTAL(D3)+MAKS(REST(D3;1);TID(22;;)));1)) In case my translation is wrong my formulas a 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 Replace @mailinator.com with @tiscali.co.uk "H. Nissen" wrote in message ... 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: 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 Replace @mailinator.com with @tiscali.co.uk "H. Nissen" wrote in message ... 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: 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 Replace @mailinator.com with @tiscali.co.uk "H. Nissen" wrote in message ... 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: 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. |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating of time
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 Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... 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)HELT AL(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(RE ST(E3;1);TID(5;;)))-(HELTAL(D3)+MAKS(REST(D3;1);TID(22;;)));1)) In case my translation is wrong my formulas a 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 Replace @mailinator.com with @tiscali.co.uk "H. Nissen" wrote in message ... 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: 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 Replace @mailinator.com with @tiscali.co.uk "H. Nissen" wrote in message ... 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: 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 Replace @mailinator.com with @tiscali.co.uk "H. Nissen" wrote in message ... 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: 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. |
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating of time
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: 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 Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... 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)HELT AL(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(RE ST(E3;1);TID(5;;)))-(HELTAL(D3)+MAKS(REST(D3;1);TID(22;;)));1)) In case my translation is wrong my formulas a 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 Replace @mailinator.com with @tiscali.co.uk "H. Nissen" wrote in message ... 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: 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 Replace @mailinator.com with @tiscali.co.uk "H. Nissen" wrote in message ... 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: 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 Replace @mailinator.com with @tiscali.co.uk "H. Nissen" wrote in message ... 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: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating Difference in Time (Amt. of Time Transpired) | Excel Discussion (Misc queries) | |||
| Calculating Regular time, overtime and double time | Excel Worksheet Functions | |||
| calculating timesheet, time-in/time-out = total hours & minutes, . | Excel Worksheet Functions | |||
| Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
| Calculating effective time from start/end date+time | Excel Worksheet Functions | |||
Linear Mode
