Slightly complex conversion and date calculation

Every week I receive an output from our database with every case we've handled the previous week, and our results wether we managed to hit the turn-around-time or not on each of them.

The layout is as follows:

Please Login or Register  to view this content.

The output is a bit weird, because three of the date columns are in the format
"wed 2008-4-14 13:05:55", while two of them are in a proper date format, ie "14.04.2008 13:05:55". Now, the two that already are in a proper format can be changed using the format cell method.
The other three cannot. No matter what format I try it will not read it as a date. =Datevalue does not recognize it.

The only way I've managed to extract the date is to use the Text-to-column conversion.

Now, the first bit:
Is there any other way I can convert the three first columns automatically to a date value?

The second bit is regarding calculation of TAT.

The TAT is calculated based on the type of case. The target time is then given in the last column. If the TAT Stop Time is passed the Target Time, the system markes the case as missed.

So far so good.
However, I want to calculate average handling time for the cases, and therefore need to calculate the difference between the Start Time, and the TAT Stop Time, and I would like to have the output in number of days, hours, minutes and seconds. Depending on what function i use I either manage to get the days or networkdays, or I get the hours, minutes, etc. How do I get them together?

I've attached a sample output of the columns in question in the file sample.xls

Hi, This should do the first bit.

Please Login or Register  to view this content.

Regards Mick

You could convert A2 to a recognisable date/time with this formula in another cell

=REPLACE(A2,1,4,"")+0

format as required

To get the total time between B2 and D2 try

=INT(D2-REPLACE(B2,1,4,""))&" days "&TEXT(D2-REPLACE(B2,1,4,""),"hh:mm:ss")

This includes all hours, do you want that or do you want to count certain hours only, e.g. excluding evenings or weekends?

Thanks a bunch for your replies.

The system automatically changes the start time in column B if the case is created out of hours in column A, so all hours can be used as long as I calculate from column B.

The conversion worked a charm.

However, when I calculate the result I get a bit weird result.
39450 days 00:00:50 for the first example.
Now, anyone spending that long to handle a case would be fired in my book!


I've attached the sample.xls again, with the suggested formulas from Daddylonglegs.

Hello Junior,

The second formula I suggested would be used on the original (unconverted data). If you use the first formula for a conversion then you don't need the second one.

Given that you now have everything in a recognisable date/time format you can get the difference between D2 and G2 with just this formula in H2

=D2-G2

and format as d:hh:mm:ss

This has one drawback in that time periods of 32 days or more won't display correctly. If that's an issue then change to this formula

=INT(D2-G2)&" days "&TEXT(D2-G2,"hh:mm:ss")

Note: the first of these is a formatted number which will be easier to use in calculation than the second which produces a text string.....