Calculating age of open cases

Hi everyone

I've spent some time looking through previous threads and found some help, but can't quite get what I need.

I am just trying to look at the age of open cases in a report. I want to know in hours (and maybe minutes) not including weekends. I have the created date and time in J2 and "Now" date and time in K2.

I've used the following that I got from another thread: =NETWORKDAYS(J2,K2-1+MOD(K2,1)-MOD(J2,1))

But it seems to be only giving me hours in 24 hour increments. For example a case raised on 22/5/09 gives a time of 72.00 this morning.

I'd be really grateful of some pointers

Slongy

Do you want to know differ. in in hours/minutes between two days?

U mean smth like this??

Richard Schollar wrote the following article some time back. I think it will clearly explain the solution:

Requirement:
Calculate the time between two date/time values (eg 22/1/2008 14:32) where you only want to return the cumulative time between the hours of 8am and 8pm (can be adjusted for any time period interested in eg 9am to 5:30pm for a working day, perhaps).

Given:
A1 ---- Start Date/Time ---- 21/1/2008 06:30
B1 ---- End Date/Time ---- 22/1/2008 09:45

Time span of interest:
Time between 8am and 6pm

Generalized Formula:
=(INT(B1)-INT(A1))*(HoursDayEnd-HoursDayStart)/24+MEDIAN(0,(HoursDayEnd-HoursDayStart)/24,MOD(B1,1)-HoursDayStart/24)-MEDIAN(0,MOD(A1,1)-HoursDayStart/24,(HoursDayEnd-HoursDayStart)/24)

Where:
HoursDayStart = 8 ---- (8am start)
HoursDayEnd = 18 ---- (6pm finish, 24 hour clock)
Note if fractions of hours required eg 8:30am start, 6:45pm finish, then you would replace HoursDayStart with 8.5 (30 minutes is 0.5 of an hour) and HoursDayEnd with 18.75 respectively.

This results in the specific formula:
=(INT(B1)-INT(A1))*(18-8)/24+MEDIAN(0,(18-8)/24,MOD(B1,1)-8/24)-MEDIAN(0,MOD(A1,1)-8/24,(18-8)/24)

Which returns 11:45 (remember to format the formula cell as [h]:mm to display an aggregate time and not a date value) - 10 hours on the 21st plus 1 hour 45 mins on the 22nd.

This will work with any date/time combination as long as the date/time in A1 is before the date/time in B1. Note that weekends are not excluded by this formula.

To exclude weekends:

=(NETWORKDAYS(A1,B1)-1)*(HoursDayEnd-HoursDayStart)/24+MEDIAN(0,(HoursDayEnd-HoursDayStart)/24,MOD(B1,1)-HoursDayStart/24)*(WEEKDAY(B1,2)<6)-MEDIAN(0,MOD(A1,1)-HoursDayStart/24,(HoursDayEnd-HoursDayStart)/24)*(WEEKDAY(A1,2)<6)

Note that this requires the installation of the Analysis Toolpak Add-In in Excel versions prior to Excel 2007.

The following works without the Analysis Toolpak:

=(SUM(INT((WEEKDAY(A1-{2,3,4,5,6})-INT(A1)+INT(B1))/7))-1)*(HoursDayEnd-HoursDayStart)/24+MEDIAN(0,(HoursDayEnd-HoursDayStart)/24,MOD(B1,1)-HoursDayStart/24)*(WEEKDAY(B1,2)<6)-MEDIAN(0,MOD(A1,1)-HoursDayStart/24,(HoursDayEnd-HoursDayStart)/24)*(WEEKDAY(A1,2)<6)

I'll have a look if you post a sample...

I mean a sample that illustrates your ranges of hours not needed.

How about:

=(((B2-A2)*24)-(INT((B2-A2)-NETWORKDAYS(A2,B2-1))*24))/24

formatted as [h]:mm

Great! Can you mark this post as solved then please.