[SOLVED] Calculating Time and Date Differences

[Darran]

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

[HiArt]

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 Data>Validation) rules on the cell ranges to make sure you don't enter dates and times in invalid formats.

[Darran]

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 Data>Validation) 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
>
>