|
Calculating working hours
Hi All,
I'm hoping someone can help me with the following problem. I need to create a formula which calculates the different between two date/time values excluding out of business hours. Here's an example. A1: 21/06/2005 10:00:00 A2: 23/06/2005 14:30:00 When using "=(B1-A1)*24" I can the result 52.5 If the business hours are between 08:30 and 17:00 the actual result required is 21.5 I hope this makes sense. Thanks in advance, Mo.. |
Try this
=(INT(A2)-INT(A1))*8.5+(MOD(A2,1)-MOD(A1,1))*24 -- HTH Bob Phillips "Mohammed Zenuwah" wrote in message ... Hi All, I'm hoping someone can help me with the following problem. I need to create a formula which calculates the different between two date/time values excluding out of business hours. Here's an example. A1: 21/06/2005 10:00:00 A2: 23/06/2005 14:30:00 When using "=(B1-A1)*24" I can the result 52.5 If the business hours are between 08:30 and 17:00 the actual result required is 21.5 I hope this makes sense. Thanks in advance, Mo.. |
Hi Bob,
Thanks for this, it worked a treat, I've justed realised I'll need to exclude weekends, how would I implement this using networkdays? Thanks in advance, Mo.. "Bob Phillips" wrote: Try this =(INT(A2)-INT(A1))*8.5+(MOD(A2,1)-MOD(A1,1))*24 -- HTH Bob Phillips "Mohammed Zenuwah" wrote in message ... Hi All, I'm hoping someone can help me with the following problem. I need to create a formula which calculates the different between two date/time values excluding out of business hours. Here's an example. A1: 21/06/2005 10:00:00 A2: 23/06/2005 14:30:00 When using "=(B1-A1)*24" I can the result 52.5 If the business hours are between 08:30 and 17:00 the actual result required is 21.5 I hope this makes sense. Thanks in advance, Mo.. |
=(NETWORKDAYS(A1,A2)-1)*8.5+(MOD(A2,1)-MOD(A1,1))*24
or =(NETWORKDAYS(A1,A2, holidays)-1)*8.5+(MOD(A2,1)-MOD(A1,1))*24 if you want to exclude holidays -- HTH Bob Phillips "Mohammed Zenuwah" wrote in message ... Hi Bob, Thanks for this, it worked a treat, I've justed realised I'll need to exclude weekends, how would I implement this using networkdays? Thanks in advance, Mo.. "Bob Phillips" wrote: Try this =(INT(A2)-INT(A1))*8.5+(MOD(A2,1)-MOD(A1,1))*24 -- HTH Bob Phillips "Mohammed Zenuwah" wrote in message ... Hi All, I'm hoping someone can help me with the following problem. I need to create a formula which calculates the different between two date/time values excluding out of business hours. Here's an example. A1: 21/06/2005 10:00:00 A2: 23/06/2005 14:30:00 When using "=(B1-A1)*24" I can the result 52.5 If the business hours are between 08:30 and 17:00 the actual result required is 21.5 I hope this makes sense. Thanks in advance, Mo.. |
Hi Bob,
thanks loads, both of them worked a treat. Thanks again, Mo.. "Bob Phillips" wrote: =(NETWORKDAYS(A1,A2)-1)*8.5+(MOD(A2,1)-MOD(A1,1))*24 or =(NETWORKDAYS(A1,A2, holidays)-1)*8.5+(MOD(A2,1)-MOD(A1,1))*24 if you want to exclude holidays -- HTH Bob Phillips "Mohammed Zenuwah" wrote in message ... Hi Bob, Thanks for this, it worked a treat, I've justed realised I'll need to exclude weekends, how would I implement this using networkdays? Thanks in advance, Mo.. "Bob Phillips" wrote: Try this =(INT(A2)-INT(A1))*8.5+(MOD(A2,1)-MOD(A1,1))*24 -- HTH Bob Phillips "Mohammed Zenuwah" wrote in message ... Hi All, I'm hoping someone can help me with the following problem. I need to create a formula which calculates the different between two date/time values excluding out of business hours. Here's an example. A1: 21/06/2005 10:00:00 A2: 23/06/2005 14:30:00 When using "=(B1-A1)*24" I can the result 52.5 If the business hours are between 08:30 and 17:00 the actual result required is 21.5 I hope this makes sense. Thanks in advance, Mo.. |
Pleasure Mo.
"Mohammed Zenuwah" wrote in message ... Hi Bob, thanks loads, both of them worked a treat. Thanks again, Mo.. "Bob Phillips" wrote: =(NETWORKDAYS(A1,A2)-1)*8.5+(MOD(A2,1)-MOD(A1,1))*24 or =(NETWORKDAYS(A1,A2, holidays)-1)*8.5+(MOD(A2,1)-MOD(A1,1))*24 if you want to exclude holidays -- HTH Bob Phillips "Mohammed Zenuwah" wrote in message ... Hi Bob, Thanks for this, it worked a treat, I've justed realised I'll need to exclude weekends, how would I implement this using networkdays? Thanks in advance, Mo.. "Bob Phillips" wrote: Try this =(INT(A2)-INT(A1))*8.5+(MOD(A2,1)-MOD(A1,1))*24 -- HTH Bob Phillips "Mohammed Zenuwah" wrote in message ... Hi All, I'm hoping someone can help me with the following problem. I need to create a formula which calculates the different between two date/time values excluding out of business hours. Here's an example. A1: 21/06/2005 10:00:00 A2: 23/06/2005 14:30:00 When using "=(B1-A1)*24" I can the result 52.5 If the business hours are between 08:30 and 17:00 the actual result required is 21.5 I hope this makes sense. Thanks in advance, Mo.. |
| All times are GMT +1. The time now is 10:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com