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  
Nortos
 
Posts: n/a
Default

This was resolved in another area, thanks for the help, 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

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
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
Help! I am stuck calculating Days, Hours, Mins please help OB1 Excel Worksheet Functions 3 November 15th 04 06:17 PM


All times are GMT +1. The time now is 07:11 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"