If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Birthdays span 2 days after daylight saving time | CajnBred | Calendar | 4 | May 5th, 2005 04:35 AM |
Seemingly (and probably) complex turnaround time | rgrantz | Running & Setting Up Queries | 1 | March 21st, 2005 09:19 AM |
What is wrong w/ this query? | Tom Ellison | Running & Setting Up Queries | 3 | November 19th, 2004 12:30 AM |
Calculate Days and Hours until a future day and time. | Dan Mancuso | Worksheet Functions | 6 | October 3rd, 2004 03:31 AM |
formula to Adding to working days | Suddes | General Discussion | 3 | July 30th, 2004 10:33 AM |