|
Working time and days
I would like to be able to caculate working time when the cell format is
4/1/2005 6:40:26 AM (not seperated into date and time) Cell A1 dd/mm/yyyy h:mm:ss (First Day) Cell A2 dd/mm/yyyy h:mm:ss (Last day) Working hours would be 8:00 to 17:00 Public holidays need to be taken out too The result should be working days hours and minutes or just hours and minutes, so that Saturday, Sunday, public holidays & between the hours of 17:00 to 8:00 to the next working day would not count. I've tried using: =((NETWORKDAYS(A1,C1)-2)*9)/24+TIME(17,00,0)-B1+D1-TIME(8,00,0) But I think this will only work if the date and time are in seperate cells. Can any one help |
Ho Nortos,
It is a bit more complicated than that. You also have to cater for - the start date/time being after 17:00 - the end date/time being before 08:00 - holidays - the start and/or end date being a holiday This should do it, assuming that you have a name called holidays with the public holidays defined =MAX(0,(NETWORKDAYS(A1,C1,holidays)-2)*9/24)+IF(ISNUMBER(MATCH(INT(A1),holid ays,0)),0,MAX(0,TIME(17,0,0)-MOD(A1,1)))+IF(ISNUMBER(MATCH(INT(C1),holidays, 0)),0,MAX(0,MOD(C1,1)-TIME(8,0,0))) -- HTH Bob Phillips "Nortos" wrote in message ... I would like to be able to caculate working time when the cell format is 4/1/2005 6:40:26 AM (not seperated into date and time) Cell A1 dd/mm/yyyy h:mm:ss (First Day) Cell A2 dd/mm/yyyy h:mm:ss (Last day) Working hours would be 8:00 to 17:00 Public holidays need to be taken out too The result should be working days hours and minutes or just hours and minutes, so that Saturday, Sunday, public holidays & between the hours of 17:00 to 8:00 to the next working day would not count. I've tried using: =((NETWORKDAYS(A1,C1)-2)*9)/24+TIME(17,00,0)-B1+D1-TIME(8,00,0) But I think this will only work if the date and time are in seperate cells. Can any one help |
That nearly worked, but on testing I'm getting a bit of extra time? see
example below Cell D1 04/01/2005 06:40 Cell E1 05/01/2005 07:40 Calculation: =MAX(0,(NETWORKDAYS(D1,E1,Holidays)-2)*9/24)+IF(ISNUMBER(MATCH(INT(D1),Holidays,0)),0,MAX(0 ,TIME(17,0,0)-MOD(D1,1)))+IF(ISNUMBER(MATCH(INT(E1),Holidays,0)) ,0,MAX(0,MOD(E1,1)-TIME(8,0,0))) Result: 10:19:34 Should be 9:00:00 I think? I'm most likely missing somthing very silly, any ideas? Really appreciate your help. Steve. "Bob Phillips" wrote: Ho Nortos, It is a bit more complicated than that. You also have to cater for - the start date/time being after 17:00 - the end date/time being before 08:00 - holidays - the start and/or end date being a holiday This should do it, assuming that you have a name called holidays with the public holidays defined =MAX(0,(NETWORKDAYS(A1,C1,holidays)-2)*9/24)+IF(ISNUMBER(MATCH(INT(A1),holid ays,0)),0,MAX(0,TIME(17,0,0)-MOD(A1,1)))+IF(ISNUMBER(MATCH(INT(C1),holidays, 0)),0,MAX(0,MOD(C1,1)-TIME(8,0,0))) -- HTH Bob Phillips "Nortos" wrote in message ... I would like to be able to caculate working time when the cell format is 4/1/2005 6:40:26 AM (not seperated into date and time) Cell A1 dd/mm/yyyy h:mm:ss (First Day) Cell A2 dd/mm/yyyy h:mm:ss (Last day) Working hours would be 8:00 to 17:00 Public holidays need to be taken out too The result should be working days hours and minutes or just hours and minutes, so that Saturday, Sunday, public holidays & between the hours of 17:00 to 8:00 to the next working day would not count. I've tried using: =((NETWORKDAYS(A1,C1)-2)*9)/24+TIME(17,00,0)-B1+D1-TIME(8,00,0) But I think this will only work if the date and time are in seperate cells. Can any one help |
Hi,
In your case: OpStart = 8:00 OpEnd = 17:00 Holidays: A range containing the holidays Total worked hours (between OpStart and OpEnd) with a checkin at A1 and a checkout at A2 is: =IF(A1A2,0,NETWORKDAYS(A1,A2,Holidays)*(OpEnd-OpStart) -IF(NETWORKDAYS(A1,A1,Holidays),MAX(0,MIN(OpEnd,MOD (A1,1))-OpStart),0) -IF(NETWORKDAYS(A2,A2,Holidays),MAX(0,OpEnd-MAX(MOD(A2,1),OpStart)),0)) Format as you see fit, perhaps [hh]:mm Regards, Daniel M. "Nortos" wrote in message ... I would like to be able to caculate working time when the cell format is 4/1/2005 6:40:26 AM (not seperated into date and time) Cell A1 dd/mm/yyyy h:mm:ss (First Day) Cell A2 dd/mm/yyyy h:mm:ss (Last day) Working hours would be 8:00 to 17:00 Public holidays need to be taken out too The result should be working days hours and minutes or just hours and minutes, so that Saturday, Sunday, public holidays & between the hours of 17:00 to 8:00 to the next working day would not count. I've tried using: =((NETWORKDAYS(A1,C1)-2)*9)/24+TIME(17,00,0)-B1+D1-TIME(8,00,0) But I think this will only work if the date and time are in seperate cells. Can any one help |
Thanks guys, this is now working great, have a great weekend, Steve.
"Nortos" wrote: I would like to be able to caculate working time when the cell format is 4/1/2005 6:40:26 AM (not seperated into date and time) Cell A1 dd/mm/yyyy h:mm:ss (First Day) Cell A2 dd/mm/yyyy h:mm:ss (Last day) Working hours would be 8:00 to 17:00 Public holidays need to be taken out too The result should be working days hours and minutes or just hours and minutes, so that Saturday, Sunday, public holidays & between the hours of 17:00 to 8:00 to the next working day would not count. I've tried using: =((NETWORKDAYS(A1,C1)-2)*9)/24+TIME(17,00,0)-B1+D1-TIME(8,00,0) But I think this will only work if the date and time are in seperate cells. Can any one help |
Revision
=MAX(0,(NETWORKDAYS(D1,E1,holidays)-2)*9/24)+IF(ISNUMBER(MATCH(INT(D1),holid ays,0)),0,MAX(0,TIME(17,0,0)-MOD(D1,1)))+IF(ISNUMBER(MATCH(INT(E1),holidays, 0)),0,MAX(0,MOD(E1,1)-TIME(8,0,0)))-MAX(0,TIME(8,0,0)-MOD(D1,1))-MAX(0,MOD(E 1,1)-TIME(17,0,0)) -- HTH Bob Phillips "Nortos" wrote in message ... That nearly worked, but on testing I'm getting a bit of extra time? see example below Cell D1 04/01/2005 06:40 Cell E1 05/01/2005 07:40 Calculation: =MAX(0,(NETWORKDAYS(D1,E1,Holidays)-2)*9/24)+IF(ISNUMBER(MATCH(INT(D1),Holid ays,0)),0,MAX(0,TIME(17,0,0)-MOD(D1,1)))+IF(ISNUMBER(MATCH(INT(E1),Holidays, 0)),0,MAX(0,MOD(E1,1)-TIME(8,0,0))) Result: 10:19:34 Should be 9:00:00 I think? I'm most likely missing somthing very silly, any ideas? Really appreciate your help. Steve. "Bob Phillips" wrote: Ho Nortos, It is a bit more complicated than that. You also have to cater for - the start date/time being after 17:00 - the end date/time being before 08:00 - holidays - the start and/or end date being a holiday This should do it, assuming that you have a name called holidays with the public holidays defined =MAX(0,(NETWORKDAYS(A1,C1,holidays)-2)*9/24)+IF(ISNUMBER(MATCH(INT(A1),holid ays,0)),0,MAX(0,TIME(17,0,0)-MOD(A1,1)))+IF(ISNUMBER(MATCH(INT(C1),holidays, 0)),0,MAX(0,MOD(C1,1)-TIME(8,0,0))) -- HTH Bob Phillips "Nortos" wrote in message ... I would like to be able to caculate working time when the cell format is 4/1/2005 6:40:26 AM (not seperated into date and time) Cell A1 dd/mm/yyyy h:mm:ss (First Day) Cell A2 dd/mm/yyyy h:mm:ss (Last day) Working hours would be 8:00 to 17:00 Public holidays need to be taken out too The result should be working days hours and minutes or just hours and minutes, so that Saturday, Sunday, public holidays & between the hours of 17:00 to 8:00 to the next working day would not count. I've tried using: =((NETWORKDAYS(A1,C1)-2)*9)/24+TIME(17,00,0)-B1+D1-TIME(8,00,0) But I think this will only work if the date and time are in seperate cells. Can any one help |
| All times are GMT +1. The time now is 08:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com