NEED HELP - Calculating dates and times

Sandy · 07-11-2005, 04:05 PM

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

**Barrie Davidson** · 07-11-2005, 04:53 PM

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

Regards,

PeterAtherton · 07-11-2005, 05:05 PM

"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

Anne Troy · 07-11-2005, 05:05 PM

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" <Sandy@discussions.microsoft.com> wrote in message
news:4E6013DE-5B24-4F03-B8E1-07F38B4E605E@microsoft.com...
> 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

JE McGimpsey · 07-11-2005, 05:05 PM

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 <5356161E-9A2B-4BF9-8D8C-9D65ABAAA5FD@microsoft.com>,
"PeterAtherton" <PeterAtherton@discussions.microsoft.com> wrote:
>
> =(C2+D2)-(A2+B2) and format as dd "days" hh:mm

JE McGimpsey · 07-11-2005, 05:05 PM

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 <8c0c0$42d2d5fa$97c5108d$16519@allthenewsgroups.com>,
"Anne Troy" <ng@officearticles.com> wrote:

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