Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating Time and Date Differences
I have some Call stats that I need assistance with. I need to calculate the
length of time a call has been open for, within an 10 working hour day. Current Data: 4 columns with the following:- CREATE DATE - in dd/mm/yyy Format CREATE TIME - in HH:mm:ss RESOLVED DATE - dd/mm/yyyy RESOLVED TIME 0 HH:mm:ss Goal: To calculate the diiference between the create time and reslove time. If a call has been open for more than a day, then 10 hours will need to be added to the total (we are measured on 10 working hours each day). If the call has been open for 2 days, then its 20 hours and so on.... If some can assist here I would greatly appreciate it. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating Time and Date Differences
Excel holds dates and times in numbers, so what you are after seems like a simple subtraction. days_open = create_date - resolve_date But... what about weekends and holidays? Answer use NetWorkDay(create_date, resolve_date, holiday_range) Note that the same day returns a value of 1, so if you need to add 10 hours for each day the number of hours to add would be: =((NetWorkDay(create_date, resolve_date, holiday_range)-1)*10 The time can be a simple subtraction hours_open = resolve_time - create_time This returns a fraction of a day (0.041667 = 1 hour) so multiply by 24 to return hours. hence the formulae you are after, in the cell, is: =((NetWorkDay(create_date, resolve_date, holiday_range)-1)*10) + ((resolve_time - create_time)*24) HTH Art P.S. I would but validation (menu DataValidation) rules on the cell ranges to make sure you don't enter dates and times in invalid formats. -- HiArt ------------------------------------------------------------------------ HiArt's Profile: http://www.excelforum.com/member.php...o&userid=19953 View this thread: http://www.excelforum.com/showthread...hreadid=345803 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating Time and Date Differences
Excellent, after a little tweeking it now works perfectly!
Thank you very much. "HiArt" wrote: Excel holds dates and times in numbers, so what you are after seems like a simple subtraction. days_open = create_date - resolve_date But... what about weekends and holidays? Answer use NetWorkDay(create_date, resolve_date, holiday_range) Note that the same day returns a value of 1, so if you need to add 10 hours for each day the number of hours to add would be: =((NetWorkDay(create_date, resolve_date, holiday_range)-1)*10 The time can be a simple subtraction hours_open = resolve_time - create_time This returns a fraction of a day (0.041667 = 1 hour) so multiply by 24 to return hours. hence the formulae you are after, in the cell, is: =((NetWorkDay(create_date, resolve_date, holiday_range)-1)*10) + ((resolve_time - create_time)*24) HTH Art P.S. I would but validation (menu DataValidation) rules on the cell ranges to make sure you don't enter dates and times in invalid formats. -- HiArt ------------------------------------------------------------------------ HiArt's Profile: http://www.excelforum.com/member.php...o&userid=19953 View this thread: http://www.excelforum.com/showthread...hreadid=345803 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Sheet - Calculating Time Differences for Totals | Excel Discussion (Misc queries) | |||
Calculating differences in time. PLs Help | Excel Discussion (Misc queries) | |||
Calculating Date AND Time Differences | Excel Worksheet Functions | |||
Calculating time differences across two (or more) dates | Excel Discussion (Misc queries) | |||
calculating time/dates differences | Excel Discussion (Misc queries) |