|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 15th, 2009, 04:09 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Calculating Time
I have a problem displaying the time (minutes) correctly beyond 60 minutes.
Fox example: B29 = 3/17/09 4:10 PM - cell formated as DATE and TYPE 3/17/09 4:10 PM C29 = 3/19/09 10:05 PM - cell formated as DATE and TYPE 3/19/09 10:05 PM D29 will display 24 hours 1075 minutes. Formula in D29 =INT(C29-B29)*24& "hours "&INT(MOD((C29-B29),1) & minutes" 2 things that I would like to have display in D29 a) is X days X hours X minutes. b) Total time to exclude the weekends as it is not a business day for us. I know that I'm mssing something here but do not know what exactly. Can anyone help. -- tech1NJ 
|
|
#2
April 15th, 2009, 04:55 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Calculating Time
Try
=INT(C29-B29)&" days "&INT((C29-B29-INT(C29-B29))*24) & " hours " &INT( ((C29-B29-INT(C29-B29))*24-INT((C29-B29-INT(C29-B29))*24))*60) & " minutes" To exclude weekends/holidays, try to use NETWORKDAYS function. -- Pl click the YES button (if you see it - don''''''''t worry if you don''t), if this answer was helpful. "tech1NJ" wrote: I have a problem displaying the time (minutes) correctly beyond 60 minutes. Fox example: B29 = 3/17/09 4:10 PM - cell formated as DATE and TYPE 3/17/09 4:10 PM C29 = 3/19/09 10:05 PM - cell formated as DATE and TYPE 3/19/09 10:05 PM D29 will display 24 hours 1075 minutes. Formula in D29 =INT(C29-B29)*24& "hours "&INT(MOD((C29-B29),1) & minutes" 2 things that I would like to have display in D29 a) is X days X hours X minutes. b) Total time to exclude the weekends as it is not a business day for us. I know that I'm mssing something here but do not know what exactly. Can anyone help. -- tech1NJ
|
|
#3
April 15th, 2009, 06:00 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Calculating Time
=NETWORKDAYS(B29,C29)-1&" days "&TEXT(C29-B29,"h \h\o\u\r\s mm
\m\i\n\u\t\e\s") (as shown in another group where the same question was multi-posted; please don't do that!) -- David Biddulph "Sheeloo" just remove all As... wrote in message ... Try =INT(C29-B29)&" days "&INT((C29-B29-INT(C29-B29))*24) & " hours " &INT( ((C29-B29-INT(C29-B29))*24-INT((C29-B29-INT(C29-B29))*24))*60) & " minutes" To exclude weekends/holidays, try to use NETWORKDAYS function. -- Pl click the YES button (if you see it - don''''''''t worry if you don''t), if this answer was helpful. "tech1NJ" wrote: I have a problem displaying the time (minutes) correctly beyond 60 minutes. Fox example: B29 = 3/17/09 4:10 PM - cell formated as DATE and TYPE 3/17/09 4:10 PM C29 = 3/19/09 10:05 PM - cell formated as DATE and TYPE 3/19/09 10:05 PM D29 will display 24 hours 1075 minutes. Formula in D29 =INT(C29-B29)*24& "hours "&INT(MOD((C29-B29),1) & minutes" 2 things that I would like to have display in D29 a) is X days X hours X minutes. b) Total time to exclude the weekends as it is not a business day for us. I know that I'm mssing something here but do not know what exactly. Can anyone help. -- tech1NJ
|
|
#4
April 15th, 2009, 06:21 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Calculating Time
Thanks David,
I certainly proved that 'when you have a hammer every problem looks like a nail.' "David Biddulph" wrote: =NETWORKDAYS(B29,C29)-1&" days "&TEXT(C29-B29,"h \h\o\u\r\s mm \m\i\n\u\t\e\s") (as shown in another group where the same question was multi-posted; please don't do that!) -- David Biddulph "Sheeloo" just remove all As... wrote in message ... Try =INT(C29-B29)&" days "&INT((C29-B29-INT(C29-B29))*24) & " hours " &INT( ((C29-B29-INT(C29-B29))*24-INT((C29-B29-INT(C29-B29))*24))*60) & " minutes" To exclude weekends/holidays, try to use NETWORKDAYS function. -- Pl click the YES button (if you see it - don''''''''t worry if you don''t), if this answer was helpful. "tech1NJ" wrote: I have a problem displaying the time (minutes) correctly beyond 60 minutes. Fox example: B29 = 3/17/09 4:10 PM - cell formated as DATE and TYPE 3/17/09 4:10 PM C29 = 3/19/09 10:05 PM - cell formated as DATE and TYPE 3/19/09 10:05 PM D29 will display 24 hours 1075 minutes. Formula in D29 =INT(C29-B29)*24& "hours "&INT(MOD((C29-B29),1) & minutes" 2 things that I would like to have display in D29 a) is X days X hours X minutes. b) Total time to exclude the weekends as it is not a business day for us. I know that I'm mssing something here but do not know what exactly. Can anyone help. -- tech1NJ
|
|
#5
April 15th, 2009, 08:39 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Calculating Time
As posted in other thread (please don't multi-post)
=NETWORKDAYS(B29,C29)-IF(MOD(C29,1)-MOD(B29,1)0,2,1)&" days "&IF(MOD(C29,1)MOD(B29,1),INT(MOD(C29-B29,1)*24-15.5),INT(MOD(C29-B29,1)*24))&" hours "&IF(MOD(C29*24,1)MOD(B29*24,1),INT(MOD(MOD(C 29-B29,1)*24-15.5,1)*60),INT(MOD((C29-B29)*24,1)*60))&" minutes" -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "tech1NJ" wrote: I have a problem displaying the time (minutes) correctly beyond 60 minutes. Fox example: B29 = 3/17/09 4:10 PM - cell formated as DATE and TYPE 3/17/09 4:10 PM C29 = 3/19/09 10:05 PM - cell formated as DATE and TYPE 3/19/09 10:05 PM D29 will display 24 hours 1075 minutes. Formula in D29 =INT(C29-B29)*24& "hours "&INT(MOD((C29-B29),1) & minutes" 2 things that I would like to have display in D29 a) is X days X hours X minutes. b) Total time to exclude the weekends as it is not a business day for us. I know that I'm mssing something here but do not know what exactly. Can anyone help. -- tech1NJ
|
|
#6
April 15th, 2009, 10:04 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Calculating Time
Thanks. I posted the 2nd one because I did not see this one come up and
thought it did not go through. Thanks again. -- tech1NJ "Luke M" wrote: As posted in other thread (please don't multi-post) =NETWORKDAYS(B29,C29)-IF(MOD(C29,1)-MOD(B29,1)0,2,1)&" days "&IF(MOD(C29,1)MOD(B29,1),INT(MOD(C29-B29,1)*24-15.5),INT(MOD(C29-B29,1)*24))&" hours "&IF(MOD(C29*24,1)MOD(B29*24,1),INT(MOD(MOD(C 29-B29,1)*24-15.5,1)*60),INT(MOD((C29-B29)*24,1)*60))&" minutes" -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "tech1NJ" wrote: I have a problem displaying the time (minutes) correctly beyond 60 minutes. Fox example: B29 = 3/17/09 4:10 PM - cell formated as DATE and TYPE 3/17/09 4:10 PM C29 = 3/19/09 10:05 PM - cell formated as DATE and TYPE 3/19/09 10:05 PM D29 will display 24 hours 1075 minutes. Formula in D29 =INT(C29-B29)*24& "hours "&INT(MOD((C29-B29),1) & minutes" 2 things that I would like to have display in D29 a) is X days X hours X minutes. b) Total time to exclude the weekends as it is not a business day for us. I know that I'm mssing something here but do not know what exactly. Can anyone help. -- tech1NJ
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
