NEED HELP - Calculating dates and times

[Guest]

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

 

[Guest]

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

 

[Anne Troy]

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

 

[JE McGimpsey]

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.

 

[JE McGimpsey]

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

 

[Barrie Davidson]

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

Regards,