• 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
    • 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

Automatically Calculate Fine Amount|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Automatically Calculate Fine Amount|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
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…Automatically Calculate Fine Amount
sp_PrintTopic sp_TopicIcon
Automatically Calculate Fine Amount
Avatar
John Nelson

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
April 24, 2020
sp_UserOfflineSmall Offline
1
April 24, 2020 - 12:38 pm
sp_Permalink sp_Print

I don't know where to place this or where to look for answers.  Don't know if it can be done easily or if it will require some sophisticated work.  Examples of the scenarios I would like to have the spreadsheet calculate are as follows:

- HOA dues received after Feb. 28th but before April 15th = no lien placed on property and no late fee.  So, homeowner will owe $300.

- HOA dues received after Feb. 28th but before May 15th = a lien will be placed on property, $14 will be added to cover the cost of the lien, additionally there will be a late fee of $50 for April.  So, homeowner will need to pay $364 (300+14+50).

- HOA dues received after Feb. 28th but before Sept. 15th = a lien will be placed on property, $14 will be added to cover the cost of the lien, additionally there will be a late fee of $50 for April, May, June, July, and Aug.  So, the homeowner will need to pay $564 (300+14+250).

- HOA dues recived after Feb. 28th but after Sept. 15th = a lien will be placed on property, $14 will be added to cover the cost of the lien, additionally there will be a late fee of $50 for April, May, June, July, Aug, and Sept. (300+14+300).

I've attached a spreadsheet of how I was thinking of setting up the spreadsheet.  I've manually placed the late fee in column H but I would like to have column H automatically calculated.  The late fee may have to be calculated over years.

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
April 24, 2020 - 11:23 pm
sp_Permalink sp_Print sp_EditHistory

Is the attached what you are after? My columns are in a kind of orange colour

Column F works out the lien date to 2 months from the due date rounded down to the 15th, column G is just an if Lien date vs calc date then column H works out the number of months failed and multiplies by 50.

Probably different ways but seems to do work so far (i really hope it is okay as its the first time i have been able to use DATEDIF - https://www.myonlinetraininghu.....on-datedif

Purfleet

Avatar
John Nelson

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
April 24, 2020
sp_UserOfflineSmall Offline
3
April 26, 2020 - 3:47 am
sp_Permalink sp_Print

Thank you for the information.  However, DATEIF doesn't appear to work for what I am trying to calculate because it appears to calculate based on the standard month where I'm trying to make calculations based on the middle of the month.  I also noticed I didn't word the examples in the text portion correctly in the 4/24/20 post so I've update the text and hopefully explained things better.  I've also updated the attached spreadsheet.  In the attached spreadsheet, I would like to have column H2 - H6 automatically calculate the months.  In the attached spreadsheet, the cells currently have the manually entered numbers I want the formula to automatically calculate.

This is my attempt to explain the scenario.  Fees have a due date of 02/28/20 (column D) and there will be a 15 day grace period before late fees are calculated.  The number of months to be used to calculate the late fee will be the difference between the "Report Date" (column A) and "Due Date" (column D).  So, payments received from 03/01/20 to 3/15/20 = 0 months for late fees, payments received 03/16/20 to 04/15/20 = 1 month for late fees, payments received 04/16/20 - 05/15/20 = 2 months for late fees, payments received 05/16/20 - 06/15/20 = 3 months for late fees, etc.).  A more detailed description, which includes all the moneys involved and the total amount due, is included below (hope I got the detail description correct this time). 

 HOA dues received after Feb. 28th (29th leap year) but before March 15th = no lien placed on property and no late fee. So, homeowner will only owe $300.
 HOA dues received after March 15th but before April 16th (payment received April 15th) = a lien will be placed on the property, $14 will be added to cover the cost for filling and removing the lien, additionally there will be a $50 late fee for March. So, the homeowner will have to pay $364 to have the lien removed ($300+14+50).
 HOA dues received after March 15th and after April 15th (payment received April 16th) = a lien will be placed on the property, $14 will be added to cover the cost for filling and removing the lien, additionally there will be a $100 late fee for March and April. So, the homeowner will have to pay $414 to have the lien removed ($300+14+100).
 HOA dues received after March 15th but before Sept. 16th (payment received Sept. 15th) = a lien will be placed on the property, $14 will be added to cover the cost for filling and removing the lien, additionally there will be a $50 late fee for March, April, May, June, July, and Aug. So, the homeowner will have to pay $614 to have the lien removed ($300+14+300).
 HOA dues received after March 15th but after Sept. 15th (payment received Sept. 16th) = a lien will be placed on the property, $14 will be added to cover the cost for filling and removing the lien, additionally there will be a $50 late fee for March, April, May, June, July, Aug, and Sept. So, the homeowner will have to pay $664 to have the lien removed ($300+14+350).

I've also updated the spreadsheet to match the above description as I made errors in the spreadsheet attached to the 04/24/20 posting as well.  The closest way I've come up with automatically calculating the months was with an IF statement but it has problems.

The numbers I manually added in cells H2 - H6 are what I want to end up with and hope there is a way of having Excel manually calculate.  The spreadsheet in rows 2 - 6 is what I want to end up with.  The speadsheet in rows 23 - 27 is where I'm attempting to figure out how to get to the spreadsheet in rows 2 - 6. 

The IF statement I created to calculate the months and the results can be found in cells H23 - H27 and then I use the TRUNC function in cells I23 - I27 to eliminate decimals so a whole number would be used in the calculation to calculate the "Late Fee Amount" and "Total Amount Due" columns.  However, the results in H23 - H27 (truncated) do not always result in the same numbers in cells H2 - H6 which are the ones I want.

At this point, I'm thinking this either can't be done or will require Excel knowledge beyond my knowledge and ability.  Any assistance is appreciated, even if it is to let me know this can't be done.

As a side note, I don't know if just replying will allow others to see my updated post and be able to provide additional thoughts on resolving this issue or if it would be better for me to start a new post.  Also, I posted this in the General forum and if it needs to be posted in another forum for more specific advice please let me know.  Any feedback on how to handle this post will be appreciated.

Thanks

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
4
April 26, 2020 - 2:58 pm
sp_Permalink sp_Print

i thought the 15th issue had been covered by the calc i added in column H but you are right the 15th/16th issue was a problem, just no examples in the test data.

As it turns out that the standard for mid month accounting is (regardless of month length) that the 15th is the current month and the 16th is the next month - if you adhere to that standard we can go with =IF(DAY(A37)<=15,DATEDIF(D37,A37,"M"),IF(DAY(A37)>=16,DATEDIF(D37,A37,"M")+1))

Avatar
John Nelson

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
April 24, 2020
sp_UserOfflineSmall Offline
5
April 27, 2020 - 11:57 pm
sp_Permalink sp_Print

Thank you for all your help.  I is greatly appreciated.  Have a good day.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: SALLY, Valentyn Kristioglo, Tiffany Kang
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Bruce Tang Nian
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Forum Stats:
Groups: 3
Forums: 24
Topics: 6223
Posts: 27294

 

Member Stats:
Guest Posters: 49
Members: 31919
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • 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
 

Company

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

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.