Calculating Time and Date Differences

[Guest]

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.

 

[Guest]

Excellent, after a little tweeking it now works perfectly!

Thank you very much.