Help needed in creating a purchase ledger???

[Tommmy]

Hi All,

I hoping someone may be able to help. I'm creating a purchase ledger and would like to include at the top all a total of:

Total Unpaid
Payments that are now overdue
Payments that fall within 30 days
Payments that fall within 14 days
Payments that fall within 7 days

Below I have tried to paste in my intentions....and any help as to how to make them work would be greatly appreciated.

Thank you...

[TABLE="width: 1423"]
<tbody>[TR]
[TD]PAYMENT LEDGER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total Unpaid[/TD]
[TD][/TD]
[TD] £ -[/TD]
[TD="colspan: 2"] Total Overdue[/TD]
[TD] £ -[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Due >30 days[/TD]
[TD] £ -[/TD]
[TD] Due>14 days[/TD]
[TD] £ -[/TD]
[TD] Due>7days[/TD]
[TD] £ -[/TD]
[/TR]
[TR]
[TD]WT#[/TD]
[TD]Invoice Date[/TD]
[TD]Supplier[/TD]
[TD]Supplier Invoice #[/TD]
[TD]Project[/TD]
[TD]Project #[/TD]
[TD]Due Date[/TD]
[TD]Date Paid[/TD]
[TD] Gross[/TD]
[TD] VAT[/TD]
[TD] Net[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10/11/2015[/TD]
[TD]ABC Test[/TD]
[TD]312623767070[/TD]
[TD][/TD]
[TD][/TD]
[TD]20/12/2015[/TD]
[TD][/TD]
[TD] £ 307.20[/TD]
[TD] £ 51.20[/TD]
[TD] £ 256.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]23/11/2015[/TD]
[TD]ZBY Supplies[/TD]
[TD]707423[/TD]
[TD][/TD]
[TD][/TD]
[TD]30/12/2015[/TD]
[TD][/TD]
[TD] £ 40.55[/TD]
[TD] £ 6.76[/TD]
[TD] £ 33.79[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]30/11/2015[/TD]
[TD]Office Supplies[/TD]
[TD]1234545[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



​

 

[Michael_E]

Some assumptions I made, when you said you wanted payments that fall within 30 days/14 days/7 days I assumed that you wanted the payments just within that range, IE: if a payment was due in 12 days you'd want it to appear in the 14 day number and not also be included in the 30 day number. I'm from America so I'm not sure which if those three values was what people had to pay, if VAT was something that comes to you, or something that the customer pays in taxes to a government, so I ended up going with the total overdue column which I think was K. the 12:16 range is just where I ended up putting the formula on my worksheet, you can obviously adjust the references to whatever you need.

Total Unpaid =SUM(range of wherever you put the next 4 formula)
Payments that are now overdue

=SUMIF(G12:G16,"<" & TODAY(),K12:K16)

Payments that fall within 30 days

=SUMIFS(K$12:K$16,G$12:G$16,"<="&TODAY()+30)-SUMIFS(K$12:K$16,G$12:G$16,"<="&TODAY()+15)

Payments that fall within 14 days

=SUMIFS(K$12:K$16,G$12:G$16,"<="&TODAY()+14) - SUMIFS(K$12:K$16,G$12:G$16,"<="&TODAY()+8)

Payments that fall within 7 days

=SUMIFS(K$12:K$16,G$12:G$16,"<="&TODAY()+7) - SUMIFS(K$12:K$16,G$12:G$16,"<="&TODAY()-1)