Excel Macro for Customer Response time

  Bren.ie 09:48 18 Nov 2003

I have an Excel spreadsheet with a list of customers with a number of transaction dates listed for each customer. Unfortunately all the transaction dates appear in one column.

I want to write a macro to subtract the initial contact date from the final contact date (and identify the number of days between both dates) for each customer.i.e. I need the macro to identify the first and last transaction dates for each customer and subtract them.

I hope I'm not looking for the impossible.

Can anyone help?

  FRANKMAC 12:44 18 Nov 2003

I am not sure how to write a macro for this or if it is possible, also you don't say what format the dates are in ....10 sept 03 or 10/09/03 etc.....

You could use "text to clolumns" to split the 2 dates into seperate columns and then use "Datedif" function to calculate the time difference.

Please post back with the format of your dates and i may be able to help further.

  Bren.ie 15:25 18 Nov 2003

The date format is dd/mm/yy i.e. 10/09/03.

One column lists the customer name, another column records the transaction type while another column contains the transaction date.

The difficulty is identifying the first and last transaction date for each customer as the total number of transactions may vary between customers.

Once the two dates can be identified it should be easy enough to subtract one from the other to give the total length of time it took to complete response to customer.

  VoG II 16:46 18 Nov 2003

Why not sort the data using the customer column as the first sort key and the date key as the second. Then, for each customer, the first and last transaction dates will be obvious.

The following will give you a single answer for one column. It is assumed that the customers name is in A1 and the dates in A2 to A11.

Sub CalcDiff()

Dim Date1 As Long, Date2 As Long

Dim myRange As Range

Dim Answer As String

Set myRange = Worksheets("Sheet1").Range("A2:A11")

Date1 = Application.WorksheetFunction.Min(myRange)

Date2 = Application.WorksheetFunction.Max(myRange)

Answer = "Date difference is " & Date2 - Date1

MsgBox (Answer)

End Sub

If you need more specific help then give me a call using the envelope

Set myRange = Worksheets("Sheet1").Range("A2:A11")

should be on a separate line

The following code assumes that you have a number of columns of dates with each column headed by the cutomers name.

It will provide a total below each column.


Sub CalcDiff()

Dim Date1 As Long, Date2 As Long

Dim myRange As Range, iCell As Range, jCell As Range

Dim LastCol As Integer, LastRow As Integer, iCnt As Integer

LastCol = Worksheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

iCnt = 1

Do Until iCnt = LastCol + 1

LastRow = Worksheets("Sheet1").Cells(Rows.Count, iCnt).End(xlUp).Row

Set iCell = Cells(2, iCnt)

Set jCell = Cells(LastRow, iCnt)

Set myRange = Worksheets("Sheet1").Range(iCell, jCell)

Date1 = Application.WorksheetFunction.Min(myRange)

Date2 = Application.WorksheetFunction.Max(myRange)

Cells(LastRow + 1, iCnt) = "Time taken"

Cells(LastRow + 2, iCnt) = Date2 - Date1

iCnt = iCnt + 1


End Sub


Hopefully the spacing will work this time!

  VoG II 22:41 18 Nov 2003

Very good but doesn't this look at all rows in the table (i.e. all customers) or am I missing something?

Just to be a sod: you don't need to use "Set" except when assigning an object. e.g

set sht = sheets("sheets1")

is required but

Set jCell = Cells(LastRow, iCnt) is not and

jCell = Cells(LastRow, iCnt)

will work equally well.

Also I think that

Date2 = Application.WorksheetFunction.Max(myRange)

can be replaced by

Date2 = Application.Max(myRange)

but these are minor niggles from one who has the inclination but not the time to attempt these problems.

Well done!

  Bren.ie 12:34 19 Nov 2003

Thank you all for your help. Unfortunately the query remains unresolved.

The customers are all listed in one column and transaction dates are also listed in one column.

After using excel to sort the customer column Excel can insert a line after each customer using "=$D2<>$D3". I was hoping that this formula could be incorporated into a macro to identify the start date and finish date for each set of customer transactions.

Any comments?

  tbh72 14:24 19 Nov 2003

Cant you just add three cells at the end of each customer so that you can calculate A. The oldest date, B. The newest date & C. A formula such as "newest date - oldest date".

If you use this proceedure you would need to insert a new row for each new transaction in order for the newly added formula to work on an ongoing basis?

  tbh72 14:26 19 Nov 2003

A. = min(range)

B. = max(range)

C. = B. - C.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Moto G6 Play Review: First Look

iPad 9.7in (2018) review

Comment utiliser Live Photos ?