Less than 6 month,6M to 1 year,1y-2years - Function With Payment & Receipt Days

mukeshkatara

New Member
Joined
Nov 29, 2014
Messages
1
How to get an age-based report in Excel if the client does not have a bill-by-bill report. I need a formula for the first invoice against the payment setup, with the balance indicated as the Following

Less than 6 months
6 months -1 year
1-2 years Query Sheet

MNK-Trading.xlsx
ABCDEFG
1MNK TRADING COMPANY
21-Apr-21 to 31-Aug-22
3DateParticularsColumn1Vch TypeVch No.DebitCredit
401-Apr-21ByOpening Balance03469074.00
508-Apr-21ToLocal SalesSales62588210.000
622-Apr-21BySBI CCReceipt00408476.00
727-Apr-21ToLocal SalesSales239630000.000
827-Apr-21BySBI CCReceipt001000000.00
910-May-21ToLocal SalesSales306691950.000
1019-May-21BySBI CCReceipt00700000.00
1110-Jun-21ToLocal SalesSales553582540.000
1224-Jun-21ToLocal SalesSales727695520.000
1316-Jul-21ToLocal SalesSales885492030.000
1417-Jul-21ToLocal SalesSales889520538.000
1520-Jul-21ToLocal SalesSales900469508.000
1603-Aug-21ToLocal SalesSales961435173.000
1721-Aug-21ToLocal SalesSales1075400838.000
1819-Oct-21ToLocal SalesSales1350996114.000
1929-Oct-21ToLocal SalesSales1418719250.000
2012-Nov-21ToLocal SalesSales1450718410.000
2115-Nov-21ToLocal SalesSalesVF/2122/SCR/0011543900.000
2218-Nov-21BySBI CCReceipt00500000.00
2305-Dec-21ToLocal SalesSalesVF/2122/SCR/0175386348.000
2408-Jan-22ToLocal SalesSalesVF/2122/SCR/0556513660.000
2510-Jan-22ToLocal SalesSalesVF/2122/SCR/0573468405.000
2618-Jan-22BySBI CCReceipt001000000.00
2723-Mar-22BySBI CCReceipt00500000.00
2831-Mar-22ToTCS COLLECTED ON SALE (0.10%)-206(1H)Debit Note(Sales)DN/2021-22/474852.000
2903-Apr-22ToLocal SalesSales(BHU)VF/2223/BHU/00101140048.000
3004-Apr-22ToLocal SalesSales(BHU)VF/2223/BHU/00161258740.000
3105-Apr-22ToLocal SalesSales(BHU)VF/2223/BHU/00201413720.000
3206-Apr-22ToLocal SalesSales(BHU)VF/2223/BHU/0021785925.000
3307-Apr-22BySBI CCReceipt00200000.00
3417-Apr-22ToLocal SalesSales(BHU)VF/2223/BHU/0067803565.000
3530-Apr-22ToTCS COLLECTED ON SALE (0.10%)-206(1H)Debit Note(Sales)DN/2022-23/02402.000
3608-May-22ToLocal SalesSalesVF/2223/SCR/01431226988.000
3721-May-22ToLocal SalesSalesVF/2223/SCR/0176887086.000
3831-May-22BySBI CCReceipt002500000.00
3905-Jun-22ToLocal SalesSalesVF/2223/SCR/02421187760.000
4007-Jun-22ToLocal SalesSalesVF/2223/SCR/02581340976.000
4107-Jun-22ToLocal SalesSales(BHU)VF/2223/BHU/00781628718.000
4216-Jun-22ToTCS COLLECTED ON SALE (0.10%)-206(1H)Debit Note(Sales)DN/2022-23/036272.000
4301-Jul-22ToLocal SalesSalesVF/2223/SCR/04181513418.000
4407-Jul-22ToLocal SalesSalesVF/2223/SCR/04401729350.000
4508-Jul-22BySBI CCReceipt002000000.00
4608-Jul-22ToLocal SalesSales(BHU)VF/2223/BHU/00931367626.000
4709-Jul-22ToLocal SalesSalesVF/2223/SCR/0443751080.000
4814-Jul-22BySBI CCReceipt002000000.00
4928-Jul-22ToLocal SalesSalesVF/2223/SCR/0508783558.000
5003-Aug-22BySBI CCReceipt002000000.00
5119-Aug-22ToLocal SalesSales(BHU)VF/2223/BHU/0110671884.000
PARKASH TRADING COMPANY
 

Attachments

  • MNK.PNG
    MNK.PNG
    91.4 KB · Views: 4

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top