NEED HELP - Calculating dates and times
 

Hello,

Below is an example of my spreadsheet...

RecvdDate RecvdTime ClosedDateClosedTimeCall Duration
9/3/2004 10:39:22 9/3/2004 12:35:37 (Need formula)
5/9/2005 16:00:52 5/12/2005 14:34:47
5/12/2005 11:57:11 5/12/2005 12:48:35
5/12/2005 12:55:53 5/12/2005 14:51:19
5/13/2005 13:39:08 5/16/2005 15:21:58
5/17/2005 16:58:59 5/25/2005 13:00:01

I need to determine the hours it takes to resolve a Help Desk issue. I hope
that you can help me with this.

Sandy

"Sandy" wrote:

Hello,

Below is an example of my spreadsheet...

RecvdDate RecvdTime ClosedDateClosedTimeCall Duration
9/3/2004 10:39:22 9/3/2004 12:35:37 (Need formula)
5/9/2005 16:00:52 5/12/2005 14:34:47
5/12/2005 11:57:11 5/12/2005 12:48:35
5/12/2005 12:55:53 5/12/2005 14:51:19
5/13/2005 13:39:08 5/16/2005 15:21:58
5/17/2005 16:58:59 5/25/2005 13:00:01

I need to determine the hours it takes to resolve a Help Desk issue. I hope
that you can help me with this.

Sandy

=(C2+D2)-(A2+B2) and format as dd "days" hh:mm

Regards
Peter

Combine the received date and time into one column, and combine the closed
date and time into one column. Then =(b1-a1)/24
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Sandy" wrote in message
...
Hello,

Below is an example of my spreadsheet...

RecvdDate RecvdTime ClosedDateClosedTimeCall Duration
9/3/2004 10:39:22 9/3/2004 12:35:37 (Need formula)
5/9/2005 16:00:52 5/12/2005 14:34:47
5/12/2005 11:57:11 5/12/2005 12:48:35
5/12/2005 12:55:53 5/12/2005 14:51:19
5/13/2005 13:39:08 5/16/2005 15:21:58
5/17/2005 16:58:59 5/25/2005 13:00:01

I need to determine the hours it takes to resolve a Help Desk issue. I
hope
that you can help me with this.

Sandy

Note that this formatting fails if the user selects (or has opened a
previous workbook set to) the 1904 date system, since "dd" is day of the
month, not elapsed days.

Likewise, if the elapsed time is greater than 31 days, the displayed
value will be wrong.


In article ,
"PeterAtherton" wrote:

=(C2+D2)-(A2+B2) and format as dd "days" hh:mm

Multiplying by 24 will give hours...


=(B2-A2)*24

But there's no need to combine the dates and times:

=(C2+D2-A2-B2)*24



In article m,
"Anne Troy" wrote:

Combine the received date and time into one column, and combine the closed
date and time into one column. Then =(b1-a1)/24

I think
=C1-A1+D1-B1
would work for you.

Regards,


--
Barrie Davidson


------------------------------------------------------------------------
Barrie Davidson's Profile: http://www.excelforum.com/member.php...o&userid=16930
View this thread: http://www.excelforum.com/showthread...hreadid=386249