Late Fees From Aged Receivable

Nov 2, 2009

See the attached spreadsheet and the comment boxes inserted containing my questions.

View 10 Replies


ADVERTISEMENT

Spreadsheet - Accounts Receivable Data

Oct 3, 2007

I have a spreadsheet that's full of Accounts Receivable data, ie customer name, invoice number, amount due, etc.

Problem is that there are hundreds of customers mixed together. I'd like to split this spreadsheet into a sheet for each customer so that I can send the individual pages out as statements.

View 9 Replies View Related

Counting Aged Tickets Based On 2 Critera

Mar 5, 2007

There are 2 sheets, the 'Open Cases by Resolver' sheet is where I am trying to gather all the relevant information from the 'Open Cases - STATS' sheet. I need to be able to group the tickets in ages, ie tickets open Inc<30, Inc 31 to 60, Inc 61 to 90, Inc >90. The spreadsheet will help explain it in better detail. I thought a ' CountIf(And' formula might work (am trying to keep it formula based) but am having trouble recognising the team name then counting the ageing tickets.

View 3 Replies View Related

Calulate Age: Find Out Over Aged Students By Date Of Birth

Oct 1, 2008

I need to find out over aged students by date of birth - now()-age started to school: DOB 8/16/2000 , Now() Age started school is 8/30/2005. Student is in grade 1. should be age 6, however, he is 8. Answer should be a little over 2 yrs.

View 2 Replies View Related

Formula- Return Number Of Aged Breaks Under Each Criteria

Jun 30, 2009

I need a formula that will return number of aged breaks under each criteria in sheet 1 row 1 and each team based on the unique case number, so for example in sheet 2 I have my raw data starting in A1, i should get the below results in sheet1 ...

View 9 Replies View Related

Calculate Fees From Table

May 29, 2008

I am trying to automate something I've been doing manually. Let me explain.

1.) I am givien information about fees associated with a mutual fund product. A sample is reproduced in F4:F8.
2.) Next, this information is entered into the table. See A4:D12.
3.) Finally, from this table, I must calculate the fees associated with various account sizes.
4.) I did this manually in cells G15:G16 for 2 different account sizes.

View 14 Replies View Related

Overdue Fees To Separate Sheet

Feb 9, 2010

I am an office worker in a (very) small business, and so we use excel as a simple database, which covers our needs easily. In particular, we use a sheet as a register of fees. The question I have involves the creation of a macro to find over-due fees.

The format of the sheet is:
A.Invoice date | B.Reference code | C.Client name | D.$$ cost | E.Due date (always 14 days from invoice date) | F.Payment received date | G.$$ Received

At current, when a fee is overdue, we manually check the list for unpaid fees past the due date and highlight them (generally yellow) and then copy and paste these to a seperate sheet (same book). I believe this could easily be accomplished with a macro, but I am not at all fluent in the coding.

What I believe the macro would require to do is sort through the list (first invoice in row 4), Check ifblank for column B (to make sure a fee exists), then check current date against due date. If 'overdue', check that G=D (received = cost). If not, highlight that row and copy to row 1 of new sheet. then return to fee sheet, move down a row, rinse and repeat untill out of fee's.

If column B is blank, then it might be the 2 row gap left between each month, so it would need to check ifblank for 3 consecutive rows before ending the macro.
If due date is blank (which happens sometimes because we enter drafts into the sheet, and just leave the dates blank until we send them out, but still has a ref code) then it should count as 'not overdue' and move on.

I think it'd be better as an on-click macro rather then an automatic macro, since it only needs to be done at certain times, not every time we fiddle with the sheet a bit. Being on-click, it could even use the same sheet to copy all the overdue's to (rather then create a new sheet every time), as long as it 'select all - delete's it before copying the new things.

Thanks for any help. I signed up just to get this help, so I deeply appologize if I have broken some sort of policy or rule.

View 8 Replies View Related

Create A Graduating Schedule To Figure Fees

Dec 1, 2008

I am using Excel 2007. I want to create a graduating schedule to figure fees. For example, $10 per thousand for the first $500,000. $13 for the next $2,000,000, etc. I have tried to understand the IF function but am not sure if this is the correct one to use.

View 9 Replies View Related

Formula Structure: The Total Fees (H2) Is A Part Of The Calculation

Apr 22, 2006

I am attempting to calculate commission (J2) based on the data entered in cell D2
1. The total fees (H2) is a part of the calculation. It represents a value from .5% to a maximum of 3%.

2. If the Loan Description is specifically ‘80/20’ then 80% of the Loan amount is used in calculating the commission. (note: ‘80/20*’ is also a valid entry). Otherwise the total loan amount is used.

D E F G H I J
Loan Dsc. Int. DSCNT% Y-S-P% Fees Loan Amount Commission
80/20 7.52.0 1.0 3.0 $137,403 $4,122.09

I attempted this formula and obviously it’s incorrect:

Calculating Commission (J2)

=IF(H2=" "," ",=IF(D2="80/20",(I2*0.80)*(H2*0.01),I2 * (H2*0.01))

1. If H2 is blank then TRUE enter a blank
2. FALSE: H2 contains a fee rate then calculate the commission Commission Calculation: If the Loan Description is 80/20 then take 80% of the loan amount and multiply it by the rate fee amount (as a percentage) to get the commission. If the Loan Description is NOT 80/20 then use the whole loan amount in the calculation.

View 3 Replies View Related

IF Statement- Spreadsheet To Calculate Referral Fees Paid To Our Agents

Jan 8, 2004

I am trying to build a spreadsheet to calculate referral fees paid to our agents. These are sliding scale fees and I need the formula to consider both the high and low range of the project value. For example we pay a certain percentages for sales in the following ranges:

Up to $5,000,000 (I got this one to work)
$5,000,001 to $7,500,000
$7,500,001 to $10,000,000
$10,000,000+

I assume the best way to do this is with an IF command but I only know how to use it for a single value, not a range. Also, if the statement is not true, then return $0.

View 9 Replies View Related

Formula To Calculate User Fees Based On Variable Rate Table

Nov 15, 2013

I am looking to setup a formula to calculate a Fee that is based on the number of users of a service in a month, with the rate varying by the number of users. The first million users in a month will bring in $0.40 per user, for users 1-4M it will bring in $0.30 per user, for users 4M-7M it will bring in .20 per user, and for users beyond 7 million it will be .10 per user. So for example, if a user count was 9 million in a month, the calculation would be ($0.40 x 1 million) + ($0.30 x 3 million) + ($0.20 x 3 million) + ($0.10 x 2 million). I know the answer to the problem is obviously $2,100,000 but I can't build the formula that solves that and can handle instances where the user count is capped in one of the individual brackets. (IE if there are 3.5M users)

I've attached an example spreadsheet : Variable Fee Schedule.xlsx‎

View 7 Replies View Related

Ebay / Amazon Profit Calculator - Making Specific Margin After Fees?

Feb 3, 2014

I don't want to appear too expensive or give my products away. If I have a part that costs me £20.00 plus vat i.e £24.00. I have to include carriage £5.00 plus vat i.e £6.00. Ebay fees for car parts including PayPal is 10%. How much would I charge to retain 20% percent after all fees and charges and what formula would I use to make the calculator.

View 2 Replies View Related

Late Coming And Early Going Calculation?

Feb 7, 2014

I want to late coming and early going of employees. eg. office incoming time is 9 am and 5 mints late coming is allowed 9.05am . and outgoing time is 6pm i am not able to calculate late coming if outgoing is 5.30pm and if put formula =IF(E3>=6,0,6-E3) not working properly.

View 1 Replies View Related

Formula For Deduction Of Minutes Late Time In And Out?

May 13, 2014

All i want is to give solution for this time in and out.how to deduct late minutes to the salary of staff.

Date IN OUT
Thu 4/3/20149:1418:39
Sat 4/5/20149:0117:56
Sun 4/6/20148:5817:58
Mon 4/7/20149:1217:50
Tue 4/8/20149:1618:00
Wed 4/9/20149:0216:06
Thu 4/10/20149:1017:54

View 5 Replies View Related

Work Marked Late -Either VBA Or Worsheet Function

Dec 30, 2006

I have a very large spreadsheet with the following columns: WO# (number field); Start date (date format MM/DD/YYYY); Frequency (text); and craft (number). I am trying to have code that checks the frequency and if is "Monthly" or "Weekly" it just goes on to the next row; if it is "Annual", it adds 163 to the start date (start date needs to changed to a numeric field); if it is "Semi-annual", it adds 82 to the start date; if it is "Quarterly", it adds 45 to the start date; and so on, there about 20m different frequencies. After it adds the above value to the start date, I need to check if that number is less than today's date (the day I run the code). If it is, it needs to flagged as "LATE" and the whole row of info copied to another worksheet with LATE as the title and all the column headings and info copied to the worksheet. I hope this makes sense to someone because I am a beginner in Excel and even less informed when it comes to VBA. Any help would be greatly appreciated.

The way the process must work is that I need to check the frequency and if it is "Weekly" or "Monthly" , it is ignored and goes on to the next row. All other frequencies are cut in half, i.e., "Annual" is 183 days, "Semi-annual is 92 days", "Quarterly" is 45 days, "2-Year" is 365 days, and so on. This number needs to added to the scheduled start date (now formatted as a number, not a date, and checked to see if it is smaller than today's date (also a number). If it is, it is reported on the second worksheet (titled Late).

View 11 Replies View Related

Syntax For Late Binding To MSFORMS.DataObject

Mar 10, 2006

I cannot figure out the syntax for the life of me.

This should work...

Dim DatObj as Object
Set DatObj = CreateObject("MSFORMS.DataObject")

...but it does not.

Also, if it is not possible to late bind to this class,

View 9 Replies View Related

String Functions Errors While Trying To Late Bind

Dec 7, 2006

I’m developing an application that will be distributed amongst several clients all running different versions of Excel.

I am trying to employ Late Binding, but am runing into an issue with some string functions (like Mid, Right and UCase) and am getting a ‘Can’t find project or library’ error.

These functions work as expected in a blank project.

View 3 Replies View Related

Show Late & On Time Based On Dates

Dec 14, 2007

I'm working with data as displayed below. My objective is to create a table by Model with summed quantitiies in two columns representing On-Time and Late. On-Time is achieved when Date Shipped is on or before the Promised Date. I have included the required format to be achieved in the second example. I'm willing to work with macros or VBA for the solution. I just need guidance to learn how to accomplish this task.

CREATE TABLES LIKE BELOW?
----B---- -----C------ --D-- ----E-----
6 Promised Date Shipped Inspection
7 Date Mod #
8 11/30/07 11/01/07 780 15216
9 10/10/07 11/01/07 230 15174
10 11/20/07 11/07/07 665 16314
11 9/13/07 11/07/07 230 15008
12 12/10/07 11/08/07 780 14452
13 11/15/07 11/08/07 780 15233
14 12/18/07 11/12/07 665 15219
15 12/3/07 11/12/07 780 15224
16 10/14/07 11/12/07 230 15011

CREATE TABLES LIKE BELOW?
-N-- ---O--- -P--
7 Mod On Time Late
8 225
9 230
10 335
11 555
12 665
13 780
14 1120

View 2 Replies View Related

Turning Cell A Background Colour If Date Is Late?

Jul 1, 2014

So I have a date in cell A1 for example.

Now if cell B1 is either blank, or more than 6 days from the date in cell A1 I want it to turn red.

View 3 Replies View Related

Formula For Calculating Late Hours And Early Goings?

Mar 7, 2014

Tried with index & Match formula but it throws errors in few cases becoz Cell value are either stored in general or H:mm formats

For ex.

Cell value "05:07" has a custom format applied (hh:mm), and in the formula bar it is displayed as "05:07:00" which is not what we want. When I change the format to 'General' Excel converts the 'time' to decimal, eg "0.213194444444444".

When I manually enter 05:07 the problem is nearly resolved as it changes to 'general' without turning to decimal, but displays as 05:07:00. The formula converts it to decimal where it throws error.

View 2 Replies View Related

Days Late Formula (result To Equal Either Zero Or Blank)

Oct 1, 2008

I have a formula in Excel2003 that is working fine except for when D12 is blank. When D12 is blank I want the result to equal either zero or blank.

=IF(ISBLANK(E12),NOW()-D12,E12-D12)

View 2 Replies View Related

Calculating Number Late Last Week From Multiple Dates / Date Sets

Jul 2, 2014

I am putting together a simple table to display current week's data vs previous weeks. The current week's data is drawn from a status chart which changes frequently. The constant change is fine for 'Current' as I only want the current data displayed.

The problem I am having is calculating the number of late jobs that existed during the previous week.

The status log has a due date which is compared to the current date to determine 'on time' status for the current week.
Due dates are reissued regularly so I can't use

=COUNTIF(RANGE,WEEKNUM(NOW()-1)) to return data about last week from my status chart.

I have available a 'Movement Log' (in the workbook but a separate worksheet) which tracks the changes in the due date field, but I'm not sure how to integrate that data to calculate the # of jobs that were running late from the last week.

My thought is that I need to perform a count of the # of late based on a comparison of 'due date' to 'date of the last day of last week' with a way to insert the "old due date" from the movement log to replace what is shown in the status log if necessary.

Movement Log.JPG

Status Chart.JPG

View 1 Replies View Related

Unable To Get Date To Flag "late" In Macro

Sep 29, 2009

I had a spreadsheet developed for me at work but the lad who developed it for me has subsequently left and I'll be honest I would not have any idea of macro's or some executables in excel. The only problem I'm having with this is it is not flagging item's as late unless they have gone over 2 months past the target date (would be ideally looking for 1 day past target date for it to flag late). Could anyone just have a quick look at this, I'm sure that this could be a quick fix but I would not have the relevant expertise for this. If someone can rectify the problem could you also just give me a quick explaination as to how this was done as I would like to have an idea myself and not have to continue to bug people with simple problems like this.

View 3 Replies View Related

Functions Left, Right, Mid And Trim: Early Binding And Late Binding

Nov 30, 2006

I have some code that run on Excel 2003, and fail on Excel 2000. It happens on functions Left, Right, Mid and Trim. I've found that I must use in "Late Bindings".
If exist any convertion for above functions?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved