Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
"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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I calculate duration between two dates and times in excel? | Excel Discussion (Misc queries) | |||
Dates and times again | Excel Worksheet Functions | |||
merge dates and times into one column? | Excel Discussion (Misc queries) | |||
Formula to calculate elapsed time between certain dates and times | Excel Discussion (Misc queries) | |||
Times and Dates | Excel Discussion (Misc queries) |