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