|
Need 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 |
Need help calculating time
Make sure the Anaylsis ToolPak Add-in is activated (Tools-addins)
=NETWORKDAYS(B29,C29)-1&" days "&INT(MOD(C29-B29,1)*24)&" hours "&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 |
Need help calculating time
or =NETWORKDAYS(B29,C29)-1&" days "&TEXT(C29-B29,"h \h\o\u\r\s mm
\m\i\n\u\t\e\s") -- David Biddulph "Luke M" wrote in message ... Make sure the Anaylsis ToolPak Add-in is activated (Tools-addins) =NETWORKDAYS(B29,C29)-1&" days "&INT(MOD(C29-B29,1)*24)&" hours "&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 |
Need help calculating time
Dear Luke,
This works great for the exception of one minor problem. It is now calculating the hours past hour normal work day (8:30 to %PM M-Fri). What can you help with to fix this additional problem. Sorry that I did not include that minor detail in the original posting. -- tech1NJ "Luke M" wrote: Make sure the Anaylsis ToolPak Add-in is activated (Tools-addins) =NETWORKDAYS(B29,C29)-1&" days "&INT(MOD(C29-B29,1)*24)&" hours "&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 |
Need help calculating time
With those time limits, how do you have an ending time of 10:05 PM?
OR Are you wanting a straight subtraction, but then have that translated into business days & hours (like a time ticket) i.e., I worked 12 hrs, which equals 1 day 3.5 hours? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "tech1NJ" wrote: Dear Luke, This works great for the exception of one minor problem. It is now calculating the hours past hour normal work day (8:30 to %PM M-Fri). What can you help with to fix this additional problem. Sorry that I did not include that minor detail in the original posting. -- tech1NJ "Luke M" wrote: Make sure the Anaylsis ToolPak Add-in is activated (Tools-addins) =NETWORKDAYS(B29,C29)-1&" days "&INT(MOD(C29-B29,1)*24)&" hours "&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 |
Need help calculating time
The 10:05 PM was a mistype on my part. It actually is 10:05 AM. The formula
you provided me works fine except that It also adds the time before 8:30AM and 5:00PM M-Fri. Is there a way to calculate the time based on the business hours mentioned above? -- tech1NJ "Luke M" wrote: With those time limits, how do you have an ending time of 10:05 PM? OR Are you wanting a straight subtraction, but then have that translated into business days & hours (like a time ticket) i.e., I worked 12 hrs, which equals 1 day 3.5 hours? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "tech1NJ" wrote: Dear Luke, This works great for the exception of one minor problem. It is now calculating the hours past hour normal work day (8:30 to %PM M-Fri). What can you help with to fix this additional problem. Sorry that I did not include that minor detail in the original posting. -- tech1NJ "Luke M" wrote: Make sure the Anaylsis ToolPak Add-in is activated (Tools-addins) =NETWORKDAYS(B29,C29)-1&" days "&INT(MOD(C29-B29,1)*24)&" hours "&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 |
Need help calculating time
I believe this works:
=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: The 10:05 PM was a mistype on my part. It actually is 10:05 AM. The formula you provided me works fine except that It also adds the time before 8:30AM and 5:00PM M-Fri. Is there a way to calculate the time based on the business hours mentioned above? -- tech1NJ "Luke M" wrote: With those time limits, how do you have an ending time of 10:05 PM? OR Are you wanting a straight subtraction, but then have that translated into business days & hours (like a time ticket) i.e., I worked 12 hrs, which equals 1 day 3.5 hours? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "tech1NJ" wrote: Dear Luke, This works great for the exception of one minor problem. It is now calculating the hours past hour normal work day (8:30 to %PM M-Fri). What can you help with to fix this additional problem. Sorry that I did not include that minor detail in the original posting. -- tech1NJ "Luke M" wrote: Make sure the Anaylsis ToolPak Add-in is activated (Tools-addins) =NETWORKDAYS(B29,C29)-1&" days "&INT(MOD(C29-B29,1)*24)&" hours "&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 |
Need help calculating time
That worked. Thanks. Pretty complicated.......
-- tech1NJ "Luke M" wrote: I believe this works: =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: The 10:05 PM was a mistype on my part. It actually is 10:05 AM. The formula you provided me works fine except that It also adds the time before 8:30AM and 5:00PM M-Fri. Is there a way to calculate the time based on the business hours mentioned above? -- tech1NJ "Luke M" wrote: With those time limits, how do you have an ending time of 10:05 PM? OR Are you wanting a straight subtraction, but then have that translated into business days & hours (like a time ticket) i.e., I worked 12 hrs, which equals 1 day 3.5 hours? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "tech1NJ" wrote: Dear Luke, This works great for the exception of one minor problem. It is now calculating the hours past hour normal work day (8:30 to %PM M-Fri). What can you help with to fix this additional problem. Sorry that I did not include that minor detail in the original posting. -- tech1NJ "Luke M" wrote: Make sure the Anaylsis ToolPak Add-in is activated (Tools-addins) =NETWORKDAYS(B29,C29)-1&" days "&INT(MOD(C29-B29,1)*24)&" hours "&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 |
Need help calculating time
Indeed. The half hour further complicated things. Actually, there is an
error. Final check on minutes should convert to hours after collecting decimals, not before. =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,1)*24<MOD(B29,1)*24,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: That worked. Thanks. Pretty complicated....... -- tech1NJ "Luke M" wrote: I believe this works: =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: The 10:05 PM was a mistype on my part. It actually is 10:05 AM. The formula you provided me works fine except that It also adds the time before 8:30AM and 5:00PM M-Fri. Is there a way to calculate the time based on the business hours mentioned above? -- tech1NJ "Luke M" wrote: With those time limits, how do you have an ending time of 10:05 PM? OR Are you wanting a straight subtraction, but then have that translated into business days & hours (like a time ticket) i.e., I worked 12 hrs, which equals 1 day 3.5 hours? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "tech1NJ" wrote: Dear Luke, This works great for the exception of one minor problem. It is now calculating the hours past hour normal work day (8:30 to %PM M-Fri). What can you help with to fix this additional problem. Sorry that I did not include that minor detail in the original posting. -- tech1NJ "Luke M" wrote: Make sure the Anaylsis ToolPak Add-in is activated (Tools-addins) =NETWORKDAYS(B29,C29)-1&" days "&INT(MOD(C29-B29,1)*24)&" hours "&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 |
| All times are GMT +1. The time now is 12:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com