Debtors-Creditors Payment Overdue (Useful) Formula in Excel

If you have a sales report in excel and wanted to know the over dues amount and remaining days for due then you can use this Formula in your excel sheet.

This formula will show the Overdues Days in Red Color, Remaining Days for Payment in Blue color and if Amount if due for Today then it will show the day in Black Color as “Today” Text.

Raw Sales Data

Raw Sales Data in Excel

You just simply copy and paste the below code in “Format Cells” option in “Custom” option as shown in the Image below-

Format code:

[Blue]# "Days Remaining";[Red]# "Days Overdue";[Black]# "Today"

Right click Format Cells Custom in Excel

Let’s break it down:

  • [Blue]# "Days Remaining": This part specifies the format for positive numbers. It will display the number with a blue font color and the text “Days Remaining” after it.
  • [Red]# "Days Overdue": This part specifies the format for negative numbers. It will display the absolute value of the number with a red font color and the text “Days Overdue” after it.
  • [Black] "Today": This part specifies the format for zero (0) or any other non-numeric entry. It will display the text “Today” with a black font color.

Output After Applying the Format Code

Payment Overdue Report in Excel

The above custom number format is designed to visually differentiate positive, negative, and zero values in a cell based on their font color and accompanying text. It’s a creative way to present data related to days remaining, days overdue, and the current day.

Video Tutorial

Download Excel File

More Excel Projects

Join Our Telegram Group techguruplus telegram group Join Our WhatsApp Group techguruplus whatsapp group
Nazim Khan - Author Image

Nazim Khan (Author) 📞 +91 9536250020
[MBA in Finance]

Nazim Khan is an expert in Microsoft Excel. He teaches people how to use it better. He has been doing this for more than ten years. He is running this website (TechGuruPlus.com) and a YouTube channel called "Business Excel" since 2016. He shares useful tips from his own experiences to help others improve their Excel skills and careers.

Leave a Comment