Customer Collection due date reminder report.

[Shabbo]

Dear Sir,
I wanted your help to make customer collection due date reminder.

• Customer name
• Expected Amount
• Due date
• Received amount

If due date is 20-11-2022 then on 19-11-2022 that column should be orange as before due date.
If due date is 20-11-2022 then on 20-11-2022 that column should be green as due date is today.
If due date is 20-11-2022 then on 21-11-2022 that column should be red as overdue date.
expected collection: Amount should be come from Column B on date basis from column C.

actual collection: Amount should come from Column D on date basis from column C.

Difference: column H minus G for date from filter.

And if possible, Add some chart as a dashboard but not necessary.

 

[ETAF]

i have added the conditional formatting and formula for the Amount tables

not sure what you need as a chart of dashboard - perhaps mock something up

3 conditional formatting rules
=$C1=TODAY()+1 - orange
=$C1=TODAY() - green
=$C1=TODAY()-1 - Red

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A1:D100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=$C1=TODAY()+1

Format [Number, Font, Border, Fill] Format Fill Orange
choose the format you would like to apply when the condition is true
OK >> OK

Repeat for the other 2 rules

For the summary table

To get the dates for today or earlier

I have used
=FILTER(C2:C30,(C2:C30<=TODAY())*(C2:C30<>""))

this will bring across all the date earlier and including today in the table

Then used

=IF(J2="","",SUMIF($C$2:$C$30,J2,B2:B30))
=IF(J2="","",SUMIF($C$2:$C$30,J2,D2:D30))

for the sums