Vb code for breaking figures and calcuation of days

[master sachin]

Hi,

i have two sheets, both are data sheets, in one sheet

a column - date , b column - payable amount

and second sheet have data like

a column - date , b column - received amount

now i wants to calculation of number of days payable amount vs received amount

any one have any idea for it.

Thanks in advance

[alansidman]

No need for VBA, a simple formula will do this. In column C of Sheet2 =A2-Sheet1!A2 and format as number, 0 decimals.

[master sachin]

Dear Master,

Just thinnk if

b column - payable amount is > then column - received amount,or

b column - payable amount is < then column - received amount,

then we need to insert new line manually and break the amount, and adjusted received amount upper or down

[alansidman]

I am confused by your request. First you ask for the difference in days and now you want to know if full payment has been received and if not then determine outstanding balance. Is this correct? If so, then I urge you to attach a sample spreadsheet with some data that shows scenarios. Then show the results manually prepared and calculated so that we can see specifically what you are needing.

It need not be a massive listing, but only several types of scenarios.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[ufopilot]

use the received date minus the payable date

this assumes the payable amount is related to the received amount in both sheets!!!
=received!A2-payable!A2
place in the cell to get the result -copy down
the names before the "!" are the sheet names - yours may differ.

[master sachin]

BOSS,

As per your order, i have attached sample file for understanding of it.Please look it, can we talk on line........

[master sachin]

Master,

in Attached file have two sheet, before working sheet and second sheet is manual calculation of days through adjusted received amount against Payable amount.

I hope you understand my point, i need to run that process for 1000 employees with bigger data base, i just give a samples of it in attached data file.

[master sachin]

i wrote the process of it,

first i used cut command the received date and received amount and move up for adjustment purpose for a particular employee, then i have checked the payable amount is bigger or small than received amount, if payable amount is bigger, then i type manual same figure in next column( adjusted) towards adjustment, then i insert new row and cut command for same for other received amount and again i have checked that payable amount is bigger or small than received amount, if payable amount is balanced figure, then i type balance figure in next column (adjusted )...... if received amount is bigger than payable amount then i type payable amount in next column (adjusted).......... same process run for all,..............please you will more clarity on attached file.

[master sachin]

any person helps me about this thread

[MarvinP]

Hi Master,

It is time to learn about Tables and Pivot Tables. You first must remove blank rows in your data. Then you need to remove rows where there is no amounts or dates.

Then create the two columns where the Payments are POSITIVE and Bills are NEGATIVE. This will allow you to do a Running Total in a Pivot Table.

Then a pivot table (see attached) is possible, and what I think you want. You will need to do some Pivot Table study to find Differences from Previous date to see how long between each pmt or bill.

You also will need to insure you sort the dates from oldest to newest.

[master sachin]

Dear Guru,

i need a day calculation against payable amt Vs. received amount on each line level, Please see the manual done calculation sheet. how i have calculated days manually and also read my previous answer on this thread about manual process............... Please kindly think about it, and do one manual step for it. so that you will be found the VB........ and think for a format according to you, where we will calculate number of days on each payable entry..........

my motive is to calculate the days against payable amt Vs. received amount on each line level, perhaps received amount excess or short, then next received amt will be adjusted on balance basis.

THANKS in advance and appreciate to you for your thinking and you will try again for generate VB CODE .

i hope you will break this thread and show that you are the best and topper.

[MarvinP]

Hi Master,

Did you read about Tables? Did you look at my answer? You don't need VBA code yet!!

If you learn about Tables then you will see your data is wrong!! You need to clean up your data (no blank rows or columns) and try again.

I've given you an answer and you simply want another one that uses VBA. You need to comment on the answer I spent time giving you before I try something else. Perhaps someone else will try to answer your question using VBA but I'm done working on it.

Also - PMing me and then not following my answer is another demerit for you. Did you know we are here to help for free? We have no obligation to help. My motivation is to teach, not answer. I'd hope to give you enough information about Excel, as a tool, that you could figure out your problem yourself, instead of simply giving you the answer.

[master sachin]

Dear Guru,

i have already see your result answer, but it is nice, but this not clear my problem, i need a formula / format / VB code, which calculate the no days on each payable line....

Sorry if you feels wrong from my side, or my replay, but i m looking a VB CODE / FORMAT / FORMULA, which calculate the no of days on each payable line, I attached a sample data, but actual data in lots of thousand rows ( like two sheet - 1 is for payable Amount , 2 is received amount), please think for wide purpose............ perhaps i m wrong to expression my question properly..........

I know you are spent your important time for it, I really respect and appreciate for your kindness for the users.

[master sachin]

any person know about vb code for this query?

[master sachin]

any person know about vb code for this query?

[master sachin]

any person helps??