Difference between row columns

[bsmer27]

Hi,

I've a data extract as below sorted by 'date of transaction' column in ASC

Ref Number	Status	Date of Transaction
150501-000062	New	05/01/2015 06:57 AM
150501-000062	Waiting at Customer	05/01/2015 08:54 AM
150501-000062	Update from Customer	05/05/2015 09:03 AM
150501-000062	Waiting at Customer	05/06/2015 01:40 AM
150501-000062	Solved	05/08/2015 01:40 PM

My requirement is to show the days/time elapsed between each status change.For e.g. For 'New' status it was 1hr 57min.Similarly for 'Waiting at Customer' it was difference between 05/01/2015 08:54 AM and 05/05/2015 09:03 AM and likewise.How can I achieve it using VBA/macros?

Thanks,
BSM

[PCI]

Try

Please Login or Register  to view this content.

[bsmer27]

Hi,

Thanks for your reply. However I noticed that when you ran the macro , 2 results were skewed.

Here is the output from column D (D2:D5).D6 value is NULL which is correct but D3 and D4 values don't seem so. Shouldn't D3 be 96hr 9min OR 4d 9m ? Similarly for D4.FYI the datetime is in MM/DD/YYYY format

01:57:00
00:09:00
16:37:00
12:00:00

Is it because of the time format?Please advice

Thanks,
B

[PCI]

Yes the datetime format was the issue
Here a remake

Please Login or Register  to view this content.

[bsmer27]

This worked.After I changed the column D>Format Cells>Time and selected Type=37:30:55 and Locale=English (United States), I could see the correct time difference

am almost close now and really appreciate your quick replies on this. We get the extract where the 'Ref Number' column changes. So after 150501-000062, there will besay 150501-000063, 150501-000064 with their own 'status' and 'date of transaction'

can you tweak your code to allow this calculation to get end after last row of 150501-000062 and start afresh with 150501-000063 and so on ?

Once again, thanks for your help

[PCI]

Which macro you want to tweak?
If i is the first one please send an update of your file with the right date format
else try

Please Login or Register  to view this content.

[bsmer27]

I tried the Treat2 sub on the sample data but the result was coming incorrect plus it was removing some contents from column A

hv attached the sample file for your perusal.Can you take a look at same?

[PCI]

See attached

[bsmer27]

am bit puzzled with excel cells formatting now

I downloaded your latest attachment but it still shows row number 4,6,9,11 for column D are still showing incorrect time values. I changed the column D>Format Cells>Time and selected Type=37:30:55 and Locale=English (United States), but they appear incorrect

For example difference between '05-05-2015 9:03:00 AM' and '05-01-2015 8:54:00 AM' is showing '29-04-1900 12:09:00 AM' in address bar for column D and appear as '00:09:00 '

[PCI]

See the file attached: There is 2 macros TReatDDMMYYYY & TreatMMDDYYYY ) one for each type of date
Note: the total number of hours and minutes is displayed, if is needed to prepare the number of days when hours are over 24, tell it.

[bsmer27]

Thanks a lot!! This was exactly what I needed