• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

AGEING REPORT|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / AGEING REPORT|General Excel Questions & Answers|Excel Forum|My Online Training Hub
                    BLACK FRIDAY SALE 20% OFF ALL COURSES
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…AGEING REPORT
sp_PrintTopic sp_TopicIcon
AGEING REPORT
Avatar
Saliha Mohamed
Member
Members
Level 0
Forum Posts: 83
Member Since:
February 2, 2013
sp_UserOfflineSmall Offline
1
November 1, 2023 - 3:38 pm
sp_Permalink sp_Print

Hi all

I have a Ageing report for supplier wise payment based on over due and i need to sum in P4 column over due based on days in (C column days)

if payment days 15 need to sum from 15 to 30 days to (> 180 days )

if payment days 30 need to sum from 30 to 45 days to (> 180 days )

Days Function
15 1 to 15 days
30 15 to 30 days
45 30 to 45 days
60 45 to 60 days
90 60 to 90 days
120 above 90
Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 886
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
2
November 3, 2023 - 9:06 am
sp_Permalink sp_Print

Hello,

I find it difficult to follow the logic here, you want to sum something overdue in P column. How do you know if it is overdue when there are no dates? You have number of days in C column, and what seems to be a lookup table, but what you give as examples in your description does not match the lookup table. And lastly, columns D to O, what are they?

Would be great if you can elaborate and give examples of expected results.

Br,
Anders

Avatar
Saliha Mohamed
Member
Members
Level 0
Forum Posts: 83
Member Since:
February 2, 2013
sp_UserOfflineSmall Offline
3
November 3, 2023 - 3:10 pm
sp_Permalink sp_Print

Hi Mr. Anders

               actually we are getting over due details from in our Tally ERP, we are identifying over due in "C" column like in a days (15,30,45,60,90,120), based on that we are summing in over dues column "P". 

               Column "D" -- we summing value from E column to O Column

               Column "O" is the greater 180 days

Avatar
Hans Hallebeek
the Netherlands
Member
Members


Trusted Members
Level 0
Forum Posts: 222
Member Since:
October 17, 2018
sp_UserOfflineSmall Offline
4
November 5, 2023 - 5:50 pm
sp_Permalink sp_Print sp_EditHistory

Your logic:

You want to keep track of every date until 120 days overdue, at least that's what you small table tells us.

The overdue dats above 90 will then, I assume be calculated based upon the current date?

Where is the date you base your calculation on?

 

What is the conten of the column OverDue its a sum of the entire row? or What?

There is no sigle mention in the table of the control date, just a tally of days, what do you want?

And not less important. WHICH VERSION OF EXCEL?  

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators
Level 1
Forum Posts: 687
Member Since:
January 31, 2022
sp_UserOnlineSmall Online
5
November 5, 2023 - 7:17 pm
sp_Permalink sp_Print

I understand that you get the ageing report (columns A to O) from your ERP system. The file you uploaded is for week number 43. It's static information based on the date when you run the report.

Column C holds the payment term for each customer and column D the total amount outstanding. The split across the various ageing columns is done in the ERP system. No Excel formulas needed here. Then, you want to sum columns F through O for amounts that are older then the payment term (i.e. over due).

In the attached file I've added a SUMIFS formula in column P that does that. Note however that I also changed the column headers to numbers representing 'up to that number of days'. That is, 15 means 'up to 15 days old'. Much easier to calculate with numbers 15, 30 etc. than with a texts "1 to 15 days", "15 to 30 days" etc.

Is that what you needed?

Avatar
Saliha Mohamed
Member
Members
Level 0
Forum Posts: 83
Member Since:
February 2, 2013
sp_UserOfflineSmall Offline
6
November 6, 2023 - 4:41 pm
sp_Permalink sp_Print

Hi Riny

I am very happy its working 100% fine. thanks you soo much.

 

Regards/Saliha

       

Avatar
Hans Hallebeek
the Netherlands
Member
Members


Trusted Members
Level 0
Forum Posts: 222
Member Since:
October 17, 2018
sp_UserOfflineSmall Offline
7
November 6, 2023 - 4:54 pm
sp_Permalink sp_Print

Nice one Riny

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators
Level 1
Forum Posts: 687
Member Since:
January 31, 2022
sp_UserOnlineSmall Online
8
November 6, 2023 - 5:32 pm
sp_Permalink sp_Print sp_EditHistory

You're welcome @Saliha

Thanks @Hans

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Riny van Eekelen, Nikki Baker, Afzaal Ahmed, Brian Sutera, Tony Brett
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
Catalin Bombea: 1864
SunnyKow: 1432
Anders Sehlstedt: 886
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 241
Hans Hallebeek: 222
Jessica Stewart: 219
A.Maurizio: 213
Newest Members:
Tony Brett
Martha Hennen
Gweneth Henderson
EDWARD JAMES
Ashley Williams
Brian Sutera
Marc Frappier
Lila Huron-Albinger
Brigitta Bildstein
Jay Quick
Forum Stats:
Groups: 3
Forums: 24
Topics: 6638
Posts: 29117

 

Member Stats:
Guest Posters: 49
Members: 33061
Moderators: 2
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Jessica
Moderators: Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.