| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating Time with Date/Time checking in formula | Excel Worksheet Functions | |||
| Calculating Regular time, overtime and double time | Excel Worksheet Functions | |||
| calculating timesheet, time-in/time-out = total hours & minutes, . | Excel Worksheet Functions | |||
| Calculating effective time from start/end date+time | Excel Worksheet Functions | |||
Linear Mode
