Compute The FuTure Date When Start Date,Lead Date,Weekly Offs,Leaves And Holidays
Nov 15, 2008
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/excel-work...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 7 Replies
ADVERTISEMENT
Feb 6, 2009
I am in need of Back-Dating in order to make payments, I am generating dates for me to deposit the payments in a Bank/Finacial Institution/Lender, however the dates generated need to be "X" days in advance if they fall on any Holidays or Week-Offs..
Now, there could be one or more Week-Offs for some Banks/Finacial Institutions/Lenders and not necessarily a Saturday and Sunday..so preferably it should be dynamic...
There would also be some Public Holidays on which the Bank would be closed ...
View 14 Replies
View Related
Nov 21, 2007
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 2 Replies
View Related
Sep 16, 2009
I'm trying to combine monthly calculations with "today" and with "workdays"
Example:
start date = 01/01/2009
today's date 09/16/2009
formula result = 10/01/2009 ; or if 10/01/2009 is a Sunday, result = 09/29/2009 (not 02/01/2009, 03/01/2009, etc)
=edate gives me a month but it doesn't skip weekends or calculate beyond today's date
View 14 Replies
View Related
Jan 8, 2009
I have a simple bike log that lists out each day of the month in rows with a summary row between months. Like this...
January Summary
Jan 1
Jan 2
Jan 3
....
Jan 31
February Summary
Feb 1
Feb 2
Feb 3
....
Feb 28
and so on. Each column has a data field where I enter in miles and distance and so on. I frequently add rows if I do two rides in a day so two rows may start with "January 23rd". I would like to have a chart that shows me my weekly ride summary. But I have some issues:
1. With the field summary I don't know the best way to do a chart that doesn't include the summary (the summary data would throw the chart way off)
2. If I have multiple rides in a day I don't know how to make the week include those days
3. I may want the week to start on Monday and not Sunday. Is it possible to have the user choose and have the chart change automatically?
View 5 Replies
View Related
Sep 11, 2009
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 = Monday-Friday, 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 14 Replies
View Related
May 25, 2014
I am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 1 Replies
View Related
Mar 9, 2014
I'm trying to workout how to take a known initial date a repeating frequency and work out the next due date from today.
Example
Initial Date :- 1st of January 2014
Frequency :- every 5 weeks
Current Date :- 9th of March 2014
Next Due date should be :-12th of March 2014 (if I worked it out correctly from my paper calendar)
I want to use a cell formula to do this for different initial dates and varying frequency periods (the frequency will always be whole weeks i.e. 1,2,3,4,5,6,7,8,8,10)
View 4 Replies
View Related
Apr 8, 2009
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 3 Replies
View Related
Sep 15, 2009
I'm trying to design an excel sheet where a user will input a date of enrollment, and basing on this date, we would like to get a followup date after one month, and again followup after 6 months. However, I would like it to skip the weekends.
I tried it using: =EDATE($C$2,1) works ok, however =EDATE($C$2,6) gives me a date on Sunday.
View 9 Replies
View Related
Jan 12, 2014
I am trying to create a calculator which will show start date and end date based on the specific date provided.
EX: column A has January 1, 2013, column B has Wednesday, Column C should have a start date which supposed to be 4 days ago (December 28).
View 6 Replies
View Related
May 7, 2014
I needs a formula (not VBA) to calculate the required start date.
I have to do a job of 14 working hours and this job must be finished on 05-may-2014 13:00
My working week is from monday u/i friday and every day I work from 08:00-16:00
At what time do I have to start the job to get it done in time.
The formula should give this result: 01-may-2014 15:00
View 3 Replies
View Related
May 26, 2014
i am trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 4 Replies
View Related
Jun 12, 2014
Trying to automate the period part of the impt function
To calculate current value of loan i have the below formula below with the 3 being the current period
=IPMT(4.3%/12,3,5*12,-7000)/(4.3%/12)
What i would like to do is for the period to be self calcuating from current date and the loan start date. I can return a value in days using start date - today() and aware month function returns the month number but stuggling to find a way to work out cumulative month from the start date.
View 4 Replies
View Related
Oct 28, 2006
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 4 Replies
View Related
Jun 26, 2007
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 5 Replies
View Related
Oct 28, 2006
We are doing a project on a booking system and wish to use VB codes to autohighlight the slots from the start date until the end date.
View 9 Replies
View Related
Jul 30, 2007
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 9 Replies
View Related
May 14, 2009
I'd like to know how I go about to key a formula to compute the due date 30days after the invoice date and How to key a formula to determine hours worked.
View 14 Replies
View Related
Jun 2, 2014
What I am trying to accomplish is, to add days from the start date (daily) and to stop adding at the end date.
Example:
(Column A) Start Date: 1/9/13
(Column B) End Date: 1/8/14
View 6 Replies
View Related
Mar 12, 2014
when I enter a start date and end date, it should autopopulate the columns with the dates in the middle including start date and end date.
View 1 Replies
View Related
May 26, 2006
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 6 Replies
View Related
Apr 29, 2009
i m making a very small calculator. It's a little hard to describe, but I need to look like the following
Let's say the individual got here on 1 Jan
Phase 1 is for days 1-14 last for 14 days
Phase 2 is for Day 15-35 last for 21 days
Phase 3 is day 36+
This is what I need it to look like: All the phase information will compute automatically after inputting the date arrived. In addition I would also like to be able to change one of the phase dates and the remainder of the phases compensate according to the newly entered date.
Date Arrived = 01 Jan 09
Phase 1 = 01jan09
Phase 2 = 04Feb09
Phase 3 = 05 Feb09
View 10 Replies
View Related
Nov 22, 2013
I want to calculate the end date of my German courses. This is how it works:
A course consists of 60 LU*. The course can occur i. e. three times a week: Monday, Wednesday and Friday. In each day the course lasts 2 LU, which means 6 LU each week. There is no course on Tuesday, Thursday, Saturday, Sunday and on holidays. Therefore this type of course that begins on 18-Nov-2013 will end on 03-Feb-2014.
Another course which occurs Tuesday, Thursday and Saturday, and respectively has 2 LU on Tuesday, 2 LU on Thursday and 3 LU on Saturday and starts on 03-Dec-2013 will end on 06-Feb-2014.
Therefore I want to create a worksheet where I set the start date, choose the days and respectively the LU amount on those days. The end date shall be calculated according to these criteria.
The workday function on excel cannot do this and I do not have any programming skills to work with VBA.
Legend:
*LU = lesson units; 1 LU is 45 minutes
Holidays:
28-Nov-13
29-Nov-13
08-Dec-13
09-Dec-13
25-Dec-13
31-Dec-13
01-Jan-14
[Code] ...........
View 7 Replies
View Related
Aug 7, 2013
I have a problem where there are 4 arguments passed to my COUNTIFS function to give me a total count i.e. "KP","James Report","Completed","Month"
Here is the Formula I used;
=COUNTIFS(Master!$B$2:$B$29,Picklist!$C$8,Master!$D$2:$D$29,B3,Master!$H$2:$H$29,Picklist!$C$2,Master!K2:K57,Picklist!E9 )
The last part of my formula is throwing an error "K2:K57"
What I should get in plain English.... "The number of 'James Report' 'Completed by 'KP' in 'August'. (Any specified month based on what comes from my "dates field" on my Master sheet.
I have got a field in my Master sheet with dates which I have transposed into the right month of the year using Month().
View 2 Replies
View Related
May 27, 2014
1. Calculate the time that has elapsed between 2 times in both hours:min (hhmm) and total mins (mm)
2. Compute the day of the week (mon-fri) a particular date fell on. I really only need to know if the date fell on a weekday or weekend.
table { }td { padding: 0px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 12pt; } 1= M-F
2=S-S
3. How to write an If statement that assign a value to time based off this chart:
table { }td { padding: 0px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Arial; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 12pt; } 1= AM (7-1459)
2=PM (15-2259)
3= MN (23-659)
View 9 Replies
View Related
Nov 24, 2012
I think I may have over engineered this formula.
$F$40 = Cell with Event Date
=IF(WEEKDAY($F$40,2)
View 2 Replies
View Related
Aug 2, 2006
I want to have a future date (30 days from today’s date) added to a cell in column A only if that cell is already blank and column N has an “O” in that same row. Here is the code that I have right now, and it works to add a value based on the conditions that I just described. I just don’t know how to get it to add a date to that is 30 days in advance of today’s date.
If Me.Cells(.Row, "N").Value = "O" And Me.Cells(.Row, "A").Value = "" Then
Me.Cells(.Row, "A").Value = "1"
End If
View 2 Replies
View Related
Dec 6, 2012
I am working as a private consultant for a company, and have put together a series of Excel applications, producing charts and providing some statistical guidelines. I will move on from here soon, and will take my work with me. I want to leave this product with the company, but it will become obsolete without some formula maintenance. Therefore, I would like to be able to have the program basically stop working at a future date. I don't really care if it freezes up, deletes itself or implodes. If not I am also afraid many copies of this program will be made and sent to others in my small community.
View 3 Replies
View Related
Nov 9, 2011
I'm trying to calculate a date in the future and subtract one day.
The date entered in N38 is 11/09/11.
The term entered in months in C8 is 24.
I want N39 to return the date 24 months in the future minus one day.
So N39 should dispay 11/08/13
How the heck do I do that?
View 5 Replies
View Related
