• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date comparison and find the ageing based on the results

T

Tharabai

Member
Hello Everyone,

I have compare the dates and display the results based on the ageing.

Below are the steps i do manually.

1. Filter "Submission" column. Filter for each value. For each value in "submission" column, filter the "TO" dates for blanks and values greater than Submission column filter. For Ex, if the submission filter is "05/15/2015" then the select all values greater than 05/15/2015 and also blanks.

2. Once Selected, copy paste the values of "from" Columns to "to" column" only for the filtered values

3. Then, find the difference between the "To" column and the "Submission" column. If the difference is >30 except for the category "TEL" mark it as "exceeded the limit" in "comments" and if the category is "TEL" mark as exceeded the limit if the value is 60 in ageing column.
 

Attachments

  • Date comparision.xlsx
    12 KB · Views: 7
Hi ,

At present , your file contains data in the To column where step 2 has already been performed.

Can you upload a file where no processing has been done , so that only the raw data is available ? This is only to understand why step 2 is required. Since step 2 is modifying cells which already contain data , this step can only be performed either manually or using VBA ; if not for this step , the other two steps can possibly be done using formulae.

Narayan
 
i have uploaded the file column TO is as is. and "To modified" is after modification
 

Attachments

  • Date comparision.xlsx
    12.3 KB · Views: 3
Hi ,

Can you clarify the following ?

E6 has the date 05-05-2015 , whereas D6 is blank. Where has this date in E6 come from ? This is just one example ; what about all the cells in column D which are blank ?

Narayan
 
Hi Narayan,

I will have only the from and To date. "From" column will not have any blank values whereas "To" column will have blank cells also.

As of now, what I will do is first filter "submitted column with a value say in this I selected 05/15/2015 for which I have to find the ageing and put comments.

for 05/15/2015 - "To" column will have dates greater or lesser than 05/15/2015 as well as blanks. I want to filter only values blank and dates greater than 05/15/2015. Then I will fill the dates the same as value in "From" column. The result is in column "To - Modified" for your reference. There will not be any "To-Modified" column just your understanding I have inserted that.

The same steps will be followed for all the dates in "submitted" column.
After that I will put ageing and put the comments as I specified in my first post.

Hope I have made clear now.
 
Hi ,

My earlier question is still not answered.

The To column has 4 dates , all of which are after the submission date of 15-05-2015. These dates are in rows 4 , 8 , 13 and 19. All other rows in the To column are blank.

I understand that all these blanks will be filled in with the dates from the From column.

However , if you take a second look at the dates in the To column , you will see that in rows 6 and 10 , the dates in the To column are different from the dates in the From column. Why ?

Narayan
 
Hi, That should not be the case. If the dates in the "To" column are greater than the filtered value in the "Submission" column then the value of "From" to be copied to "To" column. If the "To" column values are blank then the value from "From" to be copied to "To" column.

This is how I do manually. This applicable for all the values in the "Submission" column.

After which the Ageing and comments to be filled as said in the initial post.

Sorry if my requirement is confusing to you.
 
Yes Perfect... formula works fine for me... Thank you so much :)
One more query.. can we incorporate this formula in the macro.
 
Hi ,

Yes , we can. But you need to specify all aspects of the macro :

1. Which file will have the macro ?

2. What are the data ranges over which the macro will operate ?

3. Where will the output of the macro be placed ?

Narayan
 
Hi Narayan,

1. We have a template for ageing to which I have to copy paste the code to run the macro.

2. The data range are dynamic. Min rows will be 40,000 and max differs.

3. Output in the same file. We need to insert a column for ageing and one column for the comments. The one with the comments "Exceed the limit" will have to be copied to another sheet by inserting a new sheet with sheet name as "Ageing".
 
You must log in or register to reply here.
Back
Top