Macro To Calculate Billing Schedule
Jun 5, 2008
I have just been passed a new VBA project and there is one component I would like help with.
Column J lists a start date
Column K lists an end date
Column N lists a dollar amount.
Columns P through the last column has headers of month-year in text form(for example May-08)
The user enters contract information in the spreadsheet including the above cells. What they want to automate is once the user enters the contract information on a given row, she runs a macro to determine the billing schedule by calculating how many months there are between the start and end dates, divide the dollar amount in column N by that number and then prompt the user to enter what month the first payment is due, once that is entered, the macro should place the payment amount in that column and all applicable subsequent columns.
For example,
Column J = 6/1/2008
Column K = 9/1/2008
Column N = 3,000
The macro should calculate 3 months, the monthly payment amount is $1000.
A starting month prompt appears and the user enters July-08. The macro then places 1,000 in the columns for July-08, August-08 and September-08.
View 9 Replies
ADVERTISEMENT
Oct 7, 2013
How would you calculate the amount of days of a billing period for a consulting engagement by quarter when the engagement could span multiple quarters, be within a quarter or not be in a quarter at all? Project based billing
View 1 Replies
View Related
Dec 29, 2011
I want a macro that will transfer the data of billing report in the data sheet of the excel file billing dec 2011.
View 1 Replies
View Related
Dec 14, 2013
Please refer to attached sheet.
I am using the attached to schedule the employees. All I want is a formula to add up all schedule hours per week per schedule in cell R2,R3 and R4.
Book2.xlsx‎
View 2 Replies
View Related
Aug 12, 2008
I'm working on a 4-week billing calculator. I rent equipment on a day,week & month rate system. For example: Equipment 1 rents at $30 Day, $90 Week, & $270 a month. If you keep Equipment 1 for 4 days the calculator computes 4 days at a total of $120. The way a 4-week cycle works is the customer will receive the cheaper rate once the daily rate meets or exceeds the weekly rate.
So instead of $120 for 4 days, the calculator tells me to bill him for 1 week at $90 instead and the customer essentially will get the following 3 days at no extra charge until the cycle starts over. As the cycle continues, the same rules apply for the monthly rate in relation to the weekly rate & daily rate combined.
Once the weekly + daily rates add up to equal or more than the monthly, then the monthly rate is used and that's what the customer pays. What I'm trying to do is make a calculator that I input the rates and the rental period and the spread sheet will tell me (based on those rules) how many days, weeks and/or months the customer needs to pay and how much his total dollar amount will be.
View 9 Replies
View Related
Nov 15, 2008
I am trying to schedule a macro to run at 11:45 pm every time. I use system scheduler to open the excel file and when I use this command to run the macro nothing happens. The workbook opens fine but the macro does not run. I only wants to run the macro once day even if I open the workbook during the day I do not want it to run.
View 13 Replies
View Related
Sep 15, 2012
I've been experimenting with automating saving tabs that we use for billing into a .prn file. It works great on my workstation but when I run it on a coworkers workstation the file gets output to the Windows 7 Documents Library, not the workbook's file path. I want it to Save in the same file path that the workbook is saved in already.
Just to clarify I have many sheet tabs, and these are all saved in the .xlsx file. I was previously clicking one by one and doing save as, other file type, prn... but I've automated that by recording a macro and hacking away at it. specifying the workbooks file path correctly? Here's my code, truncated to just show the name of 2 of the tabs:
VB:
Sub EXPORT_ALL()
'
' EXPORT_ALL Macro
' This Macro saves the sheet then exports all tabs as a PRN.
[Code]...
View 2 Replies
View Related
Jan 28, 2003
I have a worksheet that runs a query from an external data program. I have made a macro that refreshes the worksheet and this works ok. Is there a way I can get Scheduled tasks to automatically update this workbook eg overnight. I do not want to leave the workbook open either. Can task scheduler open the workbook, refresh the data, then close the workbook again.
View 9 Replies
View Related
Jan 27, 2007
I have a script that collects data from a mySql database. Data in the database is moved with certain intervals to a bigger database (which takes longer time to run quaries against. So my thougt is to make to user able(in my excell apllication) able to schedule autorun of diffrent scripts. Do you guys know any way to schedule autorun?
View 3 Replies
View Related
Jan 15, 2009
Is there a way to take the values between C2 & D2 and have them automatically post in column F ?
View 2 Replies
View Related
Oct 15, 2008
I make our employee schedule at work, and so in Excel I have eight sheets for one workbook. (Employee schedule, then each day of the week) On each day is a table separating the shifts. Morning, Split and Night.
I was wondering if there was a way for Excel to look at the schedule sheet, and then automatically place each employee and their shift on the following day sheets. (example: Joe has 2-10 on Monday, 10-6 on Thursday and Friday) So the Excel puts Joe underneath Split 2-10 for Monday, and Morning 10-6 on Thursday and Friday.
View 10 Replies
View Related
Oct 16, 2009
We have huge restrictions on hours, and to avoid inputting, then revising the schedule into my companies scheduling module, then having to change it again when the boss looks it over, it's just confusing.
Anyway, I'll get to the point, I want to add the hours up in a row of cells for the week sunday through saturday. Like so:
1100-2100 | 1100-2100 | OFF | 1100-2100 etc... I want it to add these cells hours up and have the total end up in the 8th cell.
Is this possible? I am a new excel user, or at least, setting up my own sheet.
View 10 Replies
View Related
Dec 2, 2006
The table posted above is the schedule for my employee. I need an input box which asks for a date. When the user supplies date, it should look for people who are scheduled for that particular date and who are on leave and put the result in the next worksheet at the last occupied row.
For instance, if I supply the date as 2nd Dec then the next worksheet should be filled with @ row # 2 (Row1 has headings)
EmployeeEmp IDSchedule
A123409:00 PM - 06:00 AM
B123511:30 PM - 08:30 AM
C123602:30 AM - 11:30 AM
E123811:30 PM - 08:30 AM
G124009:00 PM - 06:00 AM
Then if I select 3rd Dec, the next worksheet should be filled with the following data starting fom row number 7 (just below the above data)
A123409:00 PM - 06:00 AM
B123511:30 PM - 08:30 AM
D123705:30 AM - 03:00 PM
G124009:00 PM - 06:00 AM
View 9 Replies
View Related
Feb 9, 2007
Excel is a new program for me sorry to say it but i have been try to figure out how to write a schedule on it and tally the hours for each employee i have them set up in rows right now and i hope i can keep it that way but i am tired of add hours up for each employee every week i'm sure it is really easy but can some one let me in no the little secret
View 9 Replies
View Related
Feb 24, 2009
Have a basic weekly schedule for 300 employees. Need to be able to identify the last day off in previous week so the following week isn't scheduled to work on the 6th or 7th day without a day off. For example, if schedule is Mon - Sun and employee #1 has Mon & Tues off this week, next week Mon would be the 6th day so I wouldn't schedule that person on Monday the following week. Calculate last column "Next Wk Mon" instead of manually figuring it out... Need to be able to identify last day off, count the days from that point to the next monday. It becomes difficult when days off are split...
MonTueWedThuFriSatSunNext Wk
23-Feb24-Feb25-Feb26-Feb27-Feb28-Feb1-MarMon
Emp 1OffOff6
Emp 2OffOff5
Emp 3OffOff4
Emp 4OffOff3
Emp 5OffOff2
Emp 6OffOff1
Emp 7OffOff1
Emp 8OffOff4
Emp 9OffOff2
View 9 Replies
View Related
Apr 6, 2009
Im having trouble with getting a macro to run at a specific time and day (Every Thursday at 7PM).
Here's what I have done so far..
1. I added my excel file to the task scheduler
2. created a class module and place the code below into it.
In testing the workbook opened but it asked if i wanted to enable or disable the macros - how do I get around this? And it does seem to kick off the macro (create_all).
Private Sub Workbook_Open()
If Hour(Now) = 1130 And Weekday(Now, vbMonday) < 7 Then
' TheNameOfYourCurrentProcedureHere
create_all
If Workbooks.Count = 1 Then
Me.Save
Application.Quit
Else
Me.Close True 'save the changes and close the workbook
End If
End If
End Sub
View 9 Replies
View Related
Jun 12, 2009
I am Cuttently creating a schedule in excel for work. the schedule will look like this:
Name.........................Shift
Andrew.......................6-3
Jonathon....................12-7
Kim............................9-5
etc etc
the thing is though this is simple enough but i am trying to use a bar chart to help me show how many people i will have on shift at anytime e.g ...
View 9 Replies
View Related
Aug 27, 2009
which is row D on my excel sheet. I need to fill into another form who works shift D, shift E and shift N. Is there a formula I can use to do this? I have given a small sample of my schedule below.
AB CD 252627ShaunD DDNicoleD EDEmilyE DECathyENEJohnNEN
View 9 Replies
View Related
Jun 26, 2014
run a spreadsheet for Vendor Deliveries and got as far as promised delivery date and actual delivery date with a late or on time comment. Im trying to get a percentage from this data.the excel spreadsheet that i have uploaded.Delivery Schedule.xls
View 5 Replies
View Related
Jan 8, 2014
Combing these two formulas. I have a work schedule spreadsheet. If the employee is Off I want the value to = 0 (zero hours). But if the cell has a start time I need it to calculate those hours. I know both of the formulas work individually. But I need them to work together.
Formula for when cell says OFF:
=IF(C11="Off",0)
And the second
Formula to calculate hours (based on start time/finish time is the same for all employees)
=($X$4-C11+($X$4<C11))*24
View 2 Replies
View Related
Mar 2, 2009
I am trying setup a maintenance schedule checklist for a large list of items. Each item has a cycle in weeks for the checklist. I set up a nested if statement to check if the item is overdue or done based on the cycle and the current date. The if statement checks when the last time the item was done or overdue and populates an "X" for the next due date. It works fine as long as the cycle is 4 or less. After that there are too many if statements and I get an error. I'd rather not have to use a macro to get this to work. Any ideas?
View 14 Replies
View Related
Jul 2, 2009
I have attahced an example of what I need to do. I need to project due dates for each of our 8 departments based on a ship date of the final product. Each of the 8 dept. have a number of operations (ops) and given number of hours for each op. These alocated hours change on every job plus I have 2 outside operations that may or maynot be added to the mix. I don't have a clue on where to even start with this formula.
View 2 Replies
View Related
Nov 21, 2009
I need to do is calculate the hours between two clock times minus 30 minutes.
Example:
Cell A1 - 4:00am
Cell B1 - 16:30
I need Cell C1 to say 12 (hours)
Also:
Cell A2 - 17:30
Cell B2 - 02:00am
I need cell c2 to say 8 (hours)
View 6 Replies
View Related
Dec 19, 2009
I have made a work schedule for my local business and have set up a series of formulas that will fill out time cards that I could print out directly onto the paper time cards. The formulas that I have work except that if there are two subsequent entries that later will not return a value and result in an error.
If you could take a look at it that would be awesome. To use it you just need to type a name into the name column and a work time into the time column for that day. then in the other sheets( one for each worker ) it will set up the time card. The the error happens on Thursday, when Bob has an entry right after Fred. Then on Bobs sheet it gives me a #N/A.
View 10 Replies
View Related
Jan 28, 2010
I'm trying to automate the calculation of my prepayment schedule. The linked image http://img402.imageshack.us/img402/2...mentqueryp.jpg shows the basic layout of how it will look. The yellow cells are going to be the only input cells but i'm unsure what formula will achieve the desired result i'm looking for across the remainder of the spreadsheet. Essentially I need the formula to look at the period (start and end dates) the invoice covers and apportion it correctly. The apportionment isn't a straight equal division per month though as it has to be calculated according to what element of the expenditure hasn't been realised yet.
For example in the car park rent line the figure of £8,000 in July is derived from the fact that that is how much hasn't effectively been incurred yet as it relates to the remainder of the invoiced period (Aug - Mar) and is calculated as 8/12 x £12000.
I hope you understand what i'm requesting, if not, let me know and i'll try to provide a better explanation.
View 7 Replies
View Related
Aug 20, 2013
I'm currently developing a model for a payment schedule. For example, lets say a customer purchases an item from a store, but the store receives that payment in the following month. I'm looking to develop a dynamic model so that if I change the assumption from 1 month to another term (2,3,4+ months etc.) the model will adjust accordingly.
I've started with a formula
=IF(MONTH(H3)-MONTH(G3)=B14,G11*B13)
But of course this will not work once the term is set to larger than 1 month. With B14 representing 1 month, and G11*B13 being payment information (price * quantity).
View 2 Replies
View Related
Jun 23, 2014
I have been trying to create a macro that would create a schedule for me based on date and a product type. In columns K-N I have an IF statement that tells when a shipment would need to arrive in a row that corresponds with a date (column A is 1/1/2015 - 12/31/2015). On the next page I would like to generate a schedule that lists the dates that all of the products are needed in chronological order. I've tried to use custom functions like finding the nth_occurence but it just gets way too messy with so many shipments.
View 6 Replies
View Related
Dec 9, 2013
After much searching today I've been unable to find a way to do this. All my searches resulted in some generic looking site looking to sell me some scheduling software.
I have a team of 7 people and I need one person to work a late shift each day of the business week. M-F
I would like a way to have the schedule automatically advance to the next person.
View 1 Replies
View Related
Apr 12, 2007
I have an excel document and I don't know which formulas to use. Here is how the document is laid out, it's an employee schedule.
The row includes A: Name B: Sunday C: Monday ... J: Total
Columns below include the employee names. What I am doing is filling out the employee schedule and I need excel to calculate scheduled hours. My problem is, when I make the schedule the hours are different than the numerical value given by excel. For example, Sunday I have an employee scheduled from 8-4. That's 8 AM until 4 PM; or a total time of 8 hours. Excel would read that as 8 (minus) 4, rendering the results useless. Once I figure out how to calculate hours by such input, I'll need to make totals which won't be as difficult.
Also, if there is a way to set the value of the word "Close" to 12:15 AM that would be extremely beneficial as well.
View 9 Replies
View Related
Sep 16, 2008
I'm trying to create a weekly employee schedule using Excel 2007 and have several issues but will start with one at a time till we get them all resolved.
I put in my start hour '6' in cell 'B7' and get this: 1/5/1900 12:00:00 AM.
I would like for to display 6AM.
I figure if I get his cell fomatted that I can get the other times correct then go on to the next issue.
View 10 Replies
View Related
