Time and Dates -

J

Jason

I need to find a way of calculating time in excel from when an entry was made and then finally finished, also need to take into consideration of working hours
Example
Received on : 01-06-2004 3p
Completed : 02-06-2004 10p
office closed from 6pm and opens 8 am, item taken 5 hours to answer during the course of opening hours
any help would be appreciated
 
J

jeff

Hey, Jason,

Try something like this:
=IF(DAY(A12)<>DAY(A11),(A12-A11)*24-14,(A12-A11))

(Rec date = A11, done date= A12; 14 hrs for closed time)

jeff
-----Original Message-----
I need to find a way of calculating time in excel from
Click to expand...
when an entry was made and then finally finished, also
need to take into consideration of working hours.
Example:
Received on : 01-06-2004 3pm
Completed : 02-06-2004 10pm
office closed from 6pm and opens 8 am, item taken 5
Click to expand...
hours to answer during the course of opening hours.
 
J

jeff

sorry, here's a better version:

=IF(DATE(YEAR(A12),MONTH(A12),DAY(A12))<>DATE(YEAR
(A11),MONTH(A11),DAY(A11)),(A12-A11)*24-(INT(A12-A11)*14),
(A12-A11)*24)

(covers more days (doesn't take weekends into account))\\

jeff
 
J

jason

Jeff,
Am i missing something, the formula is not working,
see attachment as an example

Received Completed True time Formul
24/05/2004 16:00 25/05/2004 11:00 5hrs
24/05/2004 17:00 25/05/2004 12;00 5 hrs
24/05/2004 16::00 26/05/2004 10:00 4 hrs
24/05/2004 12:00 25/05/2004 08:00 6 hrs

Open from 8 am
Closed from 6pm
 
Top