Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Nortos
 
Posts: n/a
Default 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

  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

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



  #3   Report Post  
Nortos
 
Posts: n/a
Default

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




  #4   Report Post  
Daniel.M
 
Posts: n/a
Default

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



  #5   Report Post  
Nortos
 
Posts: n/a
Default

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



  #6   Report Post  
Bob Phillips
 
Posts: n/a
Default

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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Working time and days Nortos Excel Discussion (Misc queries) 1 May 6th 05 03:47 PM
How can I calculate Vacation Time earned based on length of emplo. Kim Excel Discussion (Misc queries) 2 March 15th 05 09:04 PM
Elapsed time Calc...over 30 days long... [email protected] Excel Discussion (Misc queries) 1 December 6th 04 07:16 PM
Elapsed time Calc...over 30 days long... [email protected] Excel Discussion (Misc queries) 1 December 6th 04 06:39 PM
Elapsed time Calc...over 30 days long... [email protected] Excel Discussion (Misc queries) 0 December 6th 04 06:27 PM


All times are GMT +1. The time now is 07:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"