Calculate End Date From Start Date & Number Of Weeks
Take a look at the attachment file. Those highlighted in yellow are entered by the user. What is the formula to calculate the End date in (A6) after the user has entered the start date (A2) & the number of weeks (A4)?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Calculate What Months An Employee Worked By Start And End Date
I am working on a sheet that will allow a user to enter a start month (from a predefigned drop down list) and an end month (again from ddl). What I need to calculate is in what months the employee worked in so I can calculate their salary cost by quarter. It can populate other cells in order to do the calculation if needed. So, for example if an employee started in May, and worked until December, I need to calculate that he worked 2 months in Q1, 3 months in Q2, 3 months in Q3, and 0 months in Q4. Our fiscal year is April to March.. ;) I have thought of several options, but none of them have worked 100%.
View Replies!
View Related
Vbcodes To Select Start Date Then Begins Highlight Until End Date
i need VB to select the start date of booking and autohighlights the "blank booking slot cells" until the end date that is input in Excel. You can visualise this as a room booking system. The left most column is the dates and the first row represents the different rooms type. E.g. if one guest books the Twin Deluxe No. 123 room on 23 Oct 2006 until 25 Oct 2006. We wish the VB can essentially help us identify the start date then begins highlighting the three slots for 23, 24 and 25 Oct on the column for the Twin Deluxe Room 123.
View Replies!
View Related
Find Start Date And End Date In Range
This is a sub that uses the Find method to find a series of dates and copy them to another worksheet. The following error comes up: Object variable or With block variable not set. I have tried using a set command etc. but other errors end up coming up. Private Sub CommandButton7_Click() On Error Goto errorHandler Dim startDate As String Dim stopDate As String Dim startRow As Integer Dim stopRow As Integer startDate = InputBox("Enter the Start Date: (mm/dd/yy)") If startDate = "" Then End stopDate = InputBox("Enter the Stop Date: (mm/dd/yy)") If stopDate = "" Then End 'startDate = Format(startDate, "mm/??/yy") 'stopDate = Format(stopDate, "mm/??/yy") startRow = Me.Columns("A").Find(startDate, _ LookIn:=xlValues, lookat:=xlWhole).Row stopRow = Me.Columns("A").Find(stopDate, _ LookIn:=xlValues, lookat:=xlWhole).Row Me. Range("A" & startRow & ":A" & stopRow).Copy _ Destination:=Worksheets("Report").Range("A1") End errorHandler: MsgBox "There has been an error: " & Error() & Chr(13) _ & "Ending Sub.......Please try again", 48 End Sub
View Replies!
View Related
Sum Between Start Date & End Date
I have a small business and I keep track of all my expenditure. It is not big enough to use any Accounting software yet so I am hoping to write some macros to help speed up my time doing this. I list all my expenditure on a tab called <Daily> in the format below Date DescriptionTo £ 05/10/2006Expenses Company 6.00 07/10/2006Travel costs x 658.00 03/10/2006Stationery y 2.00 04/10/2006Other Expenditure z 365.00 (i.e. 4 columns : date, description, to, £) What I would like to do is be able to sum all these entries on a weekly basis in another tab I have already created. So for example, I would like to be able to tell the macro what range to look at (i.e. one week) then for it to look up the description and then take the sum of these totals and paste the value on a new tab.
View Replies!
View Related
Autofill From Start Date To End Date
I have one column( date) which I wanna set date range from VBA macro. The problem is I wanna use Autofill to fill from the start date to the specific end date, but with Autofill func I cannot do this. I attached a sample to explain my problem. Please take a look and give me a hint to do this.
View Replies!
View Related
Calculate The Number Of Days From Start To End
I have 2 dates a Start Date and an End Date. I need to calculate the number of days from Start to End over eight years with each of the eight years in its own column. Start End Yr1 Yr2 Yr3 11/01/99 11/01/00 11/01/01 10/31/00 10/31/01 10/31/02 Col A Col B Col C Col D Col E 10/31/00 07/05/01 365 117 0 I thought a series of IF statements but I am open to an easy solution...
View Replies!
View Related
Calculate Date Difference In Weeks
On some Military computers the Addins were not installed, therefore the WEEKNUM function cannot be used. The task is to calculate the time interval (in whole weeks) between two dates. (Every start date is to be calculated as a whole week – same for the End date). I tried but the most but came up with the incorrect formula in the attached workbook.
View Replies!
View Related
Start End Date Calculation
Need a formula that will generate the ending date of task, adding therefore if needed the number of weekend(s) in between the start and end date to the End date. Let us say: task is starting in 2008/11/11 the duration of the task is 7 days. So, we should get an End Date= 2008/11/19. 2008 11/11 11/12 11/13 11/14 11/15 11/16 11/17 11/18 11/19 Tue Wed Thu Fri Sat Sun Mon Tue Wed
View Replies!
View Related
WORKDAY Function To Calculate The Start Date
As everyone realizes that WORKDAY function can return a working date that exclude weekends and any dates identified as holidays. However, what if I want to return a working date excluding my designated holidays but including weekends? For example, Holidays are 1 Jun 2009, 2 Jun 2009 and 4 Jun 2009 Start Date: ??? Finish Date: 8 Jun 2009 Duration: 5 days The Start Date should be 30 May 2009. Seems like I am not able to use WORKDAY function to calculate the start date.
View Replies!
View Related
Compute The FuTure Date When Start Date,Lead Date,Weekly Offs,Leaves And Holidays
First and foremost I would like to congragulate you on this wondeful piece of code in the below link... The query was to get a future date excluding Fridays and Holidays... http://www.excelforum.com/excelwork...rkingdays.html I have a similar query and therefore I pasted this link... I actually wanted to get a future date using a Dynamic two day off as my the offs keep on changing as well as incorporate Holidays and Leaves if any.. Now Holidays would be official Public Holidays and Leaves would be taken by the employee.. The code needs to pick the Leaves + Holidays and different offs maybe even more than 2 offs...
View Replies!
View Related
Calculate Holidays Remaing Based On Start Date
I want to create a formula that will do the following Each worker is entitled to 21 days holiday per year this will run from 8 Jan 08 to 7 Jan 09. But if a worker starts say 15 Apr 08 he would be entitled to less than 21 days. I would just like to be able to put his start date in a cell and then automatically generate how many days holiday he would be entitled to from 15 Apr to 7 Jan.
View Replies!
View Related
Formula To Calculate Date End Of Prev Month
I have a worksheet where the current months date is entered in cell E1 (format dd/mm/yyyy) , using an input box. I need a formula the will calculate the previous months month end date in cell A6 and the month end prior to the date calculated in A6 in cell A13. where the dates start with 31 for eg 31/01/2008 , then my formula works, but if it starts with 30, 29 or 28, then obviously it does not work. I would like a formula that will calculate these previous month end dates correctly. See example below ....
View Replies!
View Related
Auto Calculate End Date Of Previous Month
I have a cell in which I will input the last day of a month (e.g., 1/31/09, 2/28/09, etc.) In another cell, I want Excel to show the last day of the previous month. Is there a formula to calculate this automatically? I enter in Cell 1: 1/31/09 Excel calculates in Cell 2: 12/28/08 I enter in Cell 1: 2/28/09 Excel calculates in Cell 2: 1/31/09
View Replies!
View Related
Calculate Future Dates From Start Date With Varying Time Period/cycle
I need to determine a formula which will allow me to calculate a future date based upon a current date with varying time periods. For example: I have a bill which is paid on the 15th and last business day of each month. I would like to be able to see the next due date regardless of what day of the week it is. I have a bill which is paid every other Tuesday. I would like to know the next due date without having to enter +14 for every due date in the future. In other words, it is preferable to be able to open the spreadsheet and automatically see the next due date, not use autofill to repeatedly add +14 to a previous date which would limit the # of future due dates that could be calculated. I have a bill which is paid on the last business day of each month, not the last Friday of each month. I would need excel to return a value for the last day of the month which = MondayFriday, regardless of what day of the week it may be as long as it isn't Saturday or Sunday(holiday exclusion would be nice but not required).
View Replies!
View Related
Calculate Start/End Times
Trying to get times worked in hours and mins, from a given start time to a given finished time. I would like H18 to read 14 and H19 to read 13.5 and so on, H23 and H24 are full 24 hour coverage, but i get a 0, when using 08:00  08:00 as we only deal with a full or half hours, the figures should read whole numbers or half number as in th example above. Also would like to total all hours worked in H28 to 117 as opposed to the figure given in the attached file. DAY / DATESTARTENDHOURS MON22May200618:008:00 AM14:00 TUE23May200617:006:30 AM13:30 WED24May200617:307:30 AM14:00 THU25May200618:007:30 AM13:30 FRI26May200618:008:00 AM14:00 SAT27May20068:008:00 AM0:00 SUN28May20068:008:00 AM0:00 TOTAL2.875 Couldnt upload the file as it was 75kbs, a
View Replies!
View Related
Calculate The Quarter To Date Number
I was given the formula below to calculate the quarter to date number. The months from 1/1/05 thru 12/31/07 are in cells C3AL3. First  can anyone explain the formula so I may understand it and possibly modify it for other uses Second  wherever the formula has 3:3 and I copy it to the next cell below it, I have to change the 4:4 to a 3:3  any suggestions on how to do this easily. By the way, the formulas are in column AO, staring on row 4. =SUM(OFFSET(A3,1,MATCH(DATE(YEAR(Date!$C$6),INT((MONTH(Date!$C$6)+2)/3)*32,1),3:3,0)1,1,MATCH(Date!$C$6,3:3,0)MATCH(DATE(YEAR(Date!$C$6),INT((MONTH(Date!$C$6)+2)/3)*32,1),3:3,0)+1))
View Replies!
View Related
Calculate Number Of Months Between Two Given Date
Start date: 12/04/2004 End date: 12/04/2006 The formula should give the answer to 24 months Example 2 Start date: 12/04/2004 End date: 13/04/2006 The formula should give the answer to 25 months When I use function =(YEAR(A4)YEAR(A3))*12+MONTH(A4)MONTH(A3), it does not show 25 months for "Example 2" as it is still within the same month "April"
View Replies!
View Related
Calculate Number Of Hours Between Two Date/time Combinations
I am trying to find a way to calculate the number of hours between date/times found in separate rows. The attached data set will help to envision what I am talking about. For each couple of rows, I need to find a way to calculate the number of hours elapsed from row 1 to row 2. In the first example, to calculate the number of hours between 12/2/2009 8:56:51 and 12/4/2009 6:35:27.
View Replies!
View Related
VB Calendar, If Date Not Within 12 Weeks, Then Msgpop Up
I can get the calendar control to come up and the value selected on the calendar to show up in a certain cell. If a user selects a date that is not within a certain 12 week range, I want a message to appear ("date not within 12 week program"), and the user can't select the date. I am storing nutrition data from a certain date the user chooses and it is a 12 week program from the start date. I want to retrieve info from those 12 weeks but do not want them to be able to select data outside the 12 weeks.
View Replies!
View Related
Date Differences In Days, Weeks Or Months
I've been racking my brain on this one for awhile, and I feel like I'm going the right direction but... I have a list of dates like: 11/30/2007 10/31/2007 9/30/2007 8/31/2007 7/31/2007 6/30/2007 5/31/2007 or maybe 11/30/2007 11/23/2007 11/16/2007 11/9/2007 11/2/2007 10/26/2007 10/19/2007 10/12/2007 10/5/2007 9/28/2007 9/21/2007 9/14/2007 9/7/2007 8/28/2007 ...and I need to determine if the difference between them is daily, weekly, or monthly. There are 79 of these lists, on 79 different sheets so I can't visually peg them as daily, weekly, or monthly. The way I have been looking at the problem is to calculate the number of months between the last & first dates and see if the resulting answer (+1) matches the number of actual dates, and so on...
View Replies!
View Related
Calculate A 30day Moving Average Based On The Last X Number Of Entries And Date
I have a worksheet that has all weekday dates in column 1 and values in column 2. I want to create a 30day moving average based on the last (nonzero) value in the column 2. Since every month has a different amount of days, I want it to search the date that has the last value (since I don't get a chance to update it daily) and go back thirsty days from that date and give an average of all the column 2 values skipping and values that are null or zero.
View Replies!
View Related
To Calculate The Difference Between The Start Time And End Time
This may be a bit vague but here goes. I have to calculate the difference between the start time and end time of a job. The only catch is, how can I avoid calculating "out of hours" time. So, if a job goes from 9am to 9am the next day, I want it to avoid calculating between the hours of 23:30 and 03:30. Another example is if a job goes from 02:00 to 04:00, I want it to avoid the tim between 02:00 and 03:00. If there is a difference in days, so the job goes overnight, how do I take that into consideration also.
View Replies!
View Related
Calculate Date Difference & Use Current Date If Cell Empty
i am trying to create a forumla in a column (D in my expamle below) to count the number of days between two dates. Knowing that col D will change everytime the @now date changes  but thats ok.. COL A COL B COL D ROW 1 Open Date Closed Date Count of days open ROW 2 4/1/07 BLANK CELL Formula result here ROW 3 4/1/07 9/5/07 Formula result here if col b has a date then subrtract col a from b and display the # result, if col b is blank then subtract col a from location I store the @ now date  say Col ZZ Row 99? so no matter there is a count of number of days in every row in column D
View Replies!
View Related
If Date Cell Less Than Todays Date Calculate Difference In Days
I have column B with a heading "Days Remaining" and column L with a heading "Deadline". starting with row 5, I need to be able to enter a date in L5 and see the days I have left, from that day untill today, on B5. I need to then be able to enter a date into L6 and see a result in B6 and on and on. Then I need to be able to insert or delete a column and have the formulas still work in the columns with the heading "Days Remaining" and "Deadline"
View Replies!
View Related
Calculate Date Six Months From Cell Date
I have a start date for a contract in cell AM1. I need AN1 show a date six months from the start date in AM1. I am fine with that part. However, I would like for AN1 to calculate not only a sixmonth date but also to autoupdate to the next six month date from AM1 once the first six month date is about, let's say, one month past. What I am doing is calculating when a sixmonth inspection needs to be completed; these inspections are ongoing, so I need them to autoupdate. So let's say a contract was awarded today, 25 JAN 2010. That would be value in AM1. I want AN1 to produce a date six months from 25 JAN 2010, which would be 25 JUL 2010 (yes, this way of calculating the six month date is fine). Then around, let's say, 25 AUG 2010, I want AN1 to autoupdate to produce the next six month date, which would be 25 JAN 2011 (six months from 25 JUL). Obviously the function would need to relate to TODAY() in some way.
View Replies!
View Related
Date Countdown: Results Of The Number Of Days Between Today And A Future Date
I am trying to get the results of the number of days between today and a future date. I am using ="cell containing futuredate"today() and it gets me the correct number of days. The problem comes in when I have yet to populate the future dates. I am getting 39991 (numeric value between today and jan 01 01) and because I am also using conditional formatting this is even more of a problem. Is there a way get excel to display nothing if it is a negative number? or to give a specified resut if the number becomes negative such as Expired or something of that nature?
View Replies!
View Related
Number Of Days To Add To Date Based On Weekday Of Date
I need to create a formula that states a delivery date when the order date is entered in an adjacent column. Items ordered on Monday, Tuesday and Wednesday will be delivered the Friday of the following week, eg. ordered 23rd April 2008, delivered on the 2nd of May 2008. Items ordered on Thursday or Friday will be delivered on a Friday 2 weeks later, eg. ordered on the 24th April, delivered on the 9th of May 2008
View Replies!
View Related
Formula For Date Minus Date = Number Of Days
I was wondering if anyone knows how I could enter a date in one cell, then another date in a second cell and in a third cell have it have it so it minuses the first date from the second and calculates the difference outputted in number of days. Example: 04/31/08  04/01/08 = 30 (days)
View Replies!
View Related
Formula Date Result Showing Number Not Date
I’m trying to resolve this issue and would appreciate some help. Scenario I have a tracking sheet that tracks development of work to be completed by individuals. I have a due date column that shows when the work should be completed. Once the work has been completed, the user enters his completion date. I have a formula that provides an overdue warning (Completion date – today’s date) and some conditional formatting. Problem When I copy the formula through all the cells in the column I get a number (example 39504) and this changes everyday. How can I eliminate this being shown as it throws out my average development day calculation?
View Replies!
View Related
