# Calculate Future Date Excluding Weekends

Aug 25, 2007
I am using the same structure to update another column, under the same sub:

If Target.Column = 5 Then '

Select Case Range("d69").Value

Case "CB1W"

ActiveCell.Value = DateAdd("ww", 1, Now())

Case "CB2W"

ActiveCell.Value = DateAdd("ww", 2, Now())

Case "CB3W"

ActiveCell.Value = DateAdd("d", 21, Now())

Case "CB4W"

ActiveCell.Value = DateAdd("d", 28, Now())

Case "CB5W"

ActiveCell.Value = DateAdd("d", 35, Now())

Case "NI"

ActiveCell.Value = ""

Case Else

ActiveCell.Value = ""

End Select

I would like to apply the code to the whole column but if I change Select Case Range("d69").Value to Select Case Range("d2:1000").Value I get a Type Mismatch Error (13) - (on Case "CB1W" ) - column d cells are data validation drop down boxes that get their value form a named ranged :Lead_Status. The column is also formatted as Text. (I applied the code to d69 because I have data already in the other cells which I don't want altered till I have the code working)

I would also greatly appreciate your advice on how to incorporate the WEEKDAY function to exclude weekends for the DateAdd function.

View 9 Replies
ADVERTISEMENT
Dec 27, 2007

I want to calculate a date that is 28 days in the future. I don't want to exclude any days - However - if the end date falls on a weekend or holiday, I would like to push it out to the next business day.

I currently have the weekends covered, but am stumped on the holidays.

(For weekends, I am using the WEEKDAY function on a hidden sheet, and then the following 3 IF statements:

IF today + 28 = Mon.-Fri., then give me today + 28.

IF today + 28 = Sat., then give me today + 30.

IF today + 28=Sun., then give me today + 29.

I have tried adding an additional IF statement to address a specific holiday - namely, President's Day on 2/18/08, which is a Monday - but it won't add the extra day, because I think my initial IF statement re: Monday being today + 28 is overriding it.

View 9 Replies
View Related
Apr 29, 2006

I am looking for formula to calculate the dates between two days (excluding the weekends)

I am working in Saudi Arabia and the weekend days over here are thursday and Friday.

View 9 Replies
View Related
Jun 8, 2008

I wanted to calculate the difference between dates, excluding the weekends. After much thought i used =NETWORKDAYS(A1,B1)+IF(NETWORKDAYS(A1,A1)+ NETWORKDAYS(B1,B1)=2,-SIGN(NETWORKDAYS(A1,B1)),0) that seemed to be working fine. However, I had a problem today with this formula when my start day was saturday (24/05/08) and the last day was monday (26/05/08). What i want is that when i take the difference between these two days, i should get zero as answer as we are not including the weekends in calculation so the start date should ideally be the first weekday which is Monday. However, the formula is giving me 1 as an answer.

This formula works fine if i select 27/05/08 as last date in which case i get 2 as an answer which is right. In gist, what i am looking for is a formula which will give me the difference between two dates and exclude the weekends from the calculation. The last date can't be a weekend, but the start date can. Hence, whenever the start date is a weekend, the formula should take monday as a start day.

View 9 Replies
View Related
Jun 2, 2014

I have a worksheet where I capture the date and time of each new entry in column A.

In the next column, it's counting up the number of hours since that entry was made. However, it's including Saturdays and Sundays in this count and I was wondering if there's a formula to calculate the number of hours excluding Saturdays and Sundays? I know that network days would count the number of working days from one date to the next, but really need hours. Is this possible?

View 3 Replies
View Related
Oct 30, 2009

How should I calculate working hours between two dates? Say if start at 9/25/2009 7:26:13 PM and finish at 10/20/2009 9:46:13 AM, the function should return 245:20:00 because the working hours are from 8am to 11 PM (8 - 23), and there are weekends between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates.

View 9 Replies
View Related
Feb 15, 2008

I am trying to calculate downtime for a Service Level Agreement.

The data that I have is the start date/time and the resolved date/time for an incident.

The data are in the format - 1/1/2008 03:32 AM. The incidents may occur at any time but downtime is calculated only business hours and excludes weekends. I may be required to exclude holidays but that is not a hard requirement right now. What I must be able to specify are the working hours.

As example(s)

- if an incident is generated on 1/2/2008 4:00 PM and resolved at 1/3/2008 11:00 AM then the downtime is 4 hours.

- if an incident is generated on 1/12/2008 4:00 PM (which is a Sat) and resolved at 1/14/2008 11:00 AM (which is Monday) then the downtime is 3 hours.

The above assumes working hours are 8:00 AM through 5:00 PM.

I have tried using the NETWORKDAYS and WORKDAY functions with little success.

View 9 Replies
View Related
Jul 16, 2013

I'm trying to have a two week rolling dates, starting on the Monday and continuing until the following Friday, this is fine if static but when the start date is then Tuesday, Wednesday etc the week then includes either the Saturday or Sunday.

From using =TODAY(), how do I populate the next ten working days without including the weekends?

View 2 Replies
View Related
Oct 10, 2008

I have a report that starts with a desired date and generates dates backwards on when certain releases should come out. I would like the code to see the date, move it back a week, check to make sure it's not a weekend or holiday, then post it in the cell below. If it does happen to land on a weekend, it should then be the Friday before the weekend. If it is a holiday, it should be the date before the holiday, as long as it is not a Sunday (or another holiday).

Attached is the workbook that I'm using. I'm starting with the desired date 'B7' and using the Holidays in a seperate worksheet. I know that in the current example, nothing will land on the weekend, unless the holiday pushes it back to one.

View 4 Replies
View Related
Feb 12, 2010

I've been reading up on Excel's date and time functions and can't really figure out the best way of doing this.

I have a total amount of time that a machine should take to finish a task. I'd like to enter a date and time into a cell (Start Date) and have another cell return the date and time that the machine should be done with the task excluding weekends, and holidays (End Date). This would be based on a certain number of "working hours" (hours in the workday minus break periods) that would be calculated in another cell.

View 9 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
Jul 25, 2007

I am trying to create a spreadsheet that will automatically calculate a date in the future.

The future date calculated needs to take the time NOW (as the user completes a field) and determines if this is before MIDNIGHT on the following SUNDAY. If so it calculates the date as a Tuesday 2 weeks on. For example... I request a date now on 1st Jan which is a Thursday. This is before midnight Sunday and so the date calculated would be Tuesday 20th January.

If the time NOW is after MIDNIGHT Sunday and before the next TUESDAY.. ie 0001hrs on the Monday and 2359hrs on the Monday then the date returned should be Tuesday 3 weeks on.. e.g 27th.

It would also need a function that would allow the total number of referrals on a Tuesday to 20 at which point it would overspill and provide a date on the Friday of the same week.. so in the first example it would be Friday 23rd

Is this something that can be achieved with formulas and a set of dates for it to calculate from or do I need to find someone who can do it in VBA for me.

View 9 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
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
Mar 21, 2013

I am generating a spreadsheet that focuses on a target opening date. There is about 12 other headings along the top all stating the different phases of construction, permitting, etc. I need to exclude weekends and holidays from most of these columns, and only exclude holidays from about 2 of the columns. I think I am to use the formula for NETWORKDAYS, but not sure how or where to incorporate it.

View 9 Replies
View Related
Apr 9, 2007

I currently have a formula like this:

e87-7+choose(weekday(e87-7),1,0,0,0,0,0,2

this formula is used because I was adding or subtracting a certain number of days from one date to figure out when a certain event would occur next. When the date fell on a weekend, I wanted to make sure that it rolled over to the next closest weekday.

HOWEVER, what I also want this formula to do is exclude weekends in the counting of the days. So basically, I want to exclude weekends completely from my formula.

example, Printing always occurs 4 days after the p.r. meeting. the p.r. meeting is in e87. so e87 + 4 = the printing date. but I dont want those 4 days to include weekends in the count AND I dont want the final date to fall on a weekend either.

View 9 Replies
View Related
Oct 17, 2006

I have a spreadsheet with bloomberg's auto feed, so in col A&B, the price will be updated everyday. I would like to calculate the Month-to- Date in Col C

e.g. MTD for today's closing = (today's closing price - last month end price)/last month end price

how can I constructure a formular in col C which will calculate the MTD automatically when a new price is added to the top everyday.

View 3 Replies
View Related
Sep 13, 2007

I want to get the list of days which are only weekdays ( excluding sat, sun but includes holidays during weekdays) I have tried WORKDAY function but it is not exactly i want coz it excludes holidays during weekdays as well.

View 5 Replies
View Related
May 22, 2008

how to find the number of days between 2 Dates excluding weekends and holidays using vba. I m using excel 2003. All I know is to figure out todays date using the keyword 'Date'.

Also, this is my second post and I have tried to comply with the rules regarding the title, but if it is still inappropriate and does not meet 'stating the problem' criteria,

View 3 Replies
View Related
Dec 4, 2013

I have a holiday and sickness chart for all of the guys in my maintenance department for the whole of next year (2014). The holidays part works fine. The sickness is split into two areas; the number of occurrences of sickness and the total number of days sick. To make the formula work for the number of occurrences it is necessary to enter sick in both Sat and Sun if the sickness spans the weekend. But we do not work the weekend and so I do not want to count those two days. The formula needs to cover the entire year (cells BL5:ZL5). The calculation that I am currently using is =COUNTIF(BL5:ZL5,"Sick")/2 (divided by two as we log a.m. and p.m. and the answer is in days). In the example attached, it would be 1 occurrence of sickness and 3 days sick, not 4 as shown.

For completeness, the formula that I am using for the occurrences is =SUMPRODUCT((BL5:ZL5="Sick")*(BL5:ZL5<>BK5:ZK5))

View 3 Replies
View Related
Jun 9, 2007

I am trying to set up a sick leave list which will work out the cumulative total for the last 12 months (from last day of last sick entry). The problem I have, as the is is added to, I need a function/formula to work out 12 months from last date and then add up the no. of sick days from then to the last date. Hope this is clear. The next problem is I need it to work out total days and then without weekends. Below is the chart I have to work out.

Column 1 Start date of sickness

column 2 End date

Column 3 the number of days sick in period (column 2 less column 1 in days)

column 4 the number of days sick in period above excluding week ends (column

2 less column 1, less weekends in days)

column 5 total sick days in last 12 months from date in column 2 going back

a year including weekends

column 6 total sick days in last 12 months from date in column 2 going back

a year excluding weekends

What function or formulas can I use to calculate column 3,4,5 & 6. I am a not very experienecd in Excel

View 7 Replies
View Related
Feb 26, 2014

Is there a way to autofill dates in excel to exclude weekends and bank holidays?

View 6 Replies
View Related
Dec 5, 2013

I am trying to run a macro to put in the Month, Day, Year on each tab but I want it to exclude holidays and weekends. I am not a techie or anything but I would like to know how to do this. I have tried various vb codes but they don't exclude the weekends/holidays.

View 3 Replies
View Related
Aug 6, 2012

I want to create a formula that works out the number of days between two dates but excludes weekends?

View 3 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
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
View Related
Dec 29, 2013

I am working on a sheet right now where I want to put the day a certain number of days in the future but I want to skip only Holidays, or days in a list on another part of the sheet, but count the weekend days.

All the formulas and post I have read is about someone wanting to skip weekends too. I did find a workdays formula where you and pick what you want to be considered as the weekend but I just want to skip holidays.

Example:

Day is Dec-31-2013 7 days in the future is Jan-7-2014

I want it to be since Jan 1 is a Holiday the answer will be Jan-8-2014

View 4 Replies
View Related
Jun 12, 2009

I've written the following code. It calculates the future value of required minimum distributions for traditional IRAs. When I enter the function in excel, it drops the last argument. I've tried changing the order of the arguments, and it always drops the last one. The arguments are : marginalTax (marginal tax rate), r (expected return on investment), t (time), begAge (beginning age), RDT (as range -- required distribution table; looks up the percentage required at each age), IRAValueat70 (calcualtes the value of the IRA at 70 years of age)

Function RMD(r As Double, marginalTax As Double, begAge As Integer, t As Integer, RDT As Range, IRAValueat70 As Long)

age = begAge

endAge = begAge + t

Dim holder As Long

Dim RMDnow As Long

Dim RMDall As Long

hat = 70

If endAge < 69 Then

Else

If begAge <= 69 Then

Do While hat < (endAge)

RMDnow = (IRAValue / (Application.VLookup(age, RDT, 2, False)))

RMDnow = (1 - marginalTax) * RMDnow...........................

View 2 Replies
View Related
Feb 9, 2009

If I have the start date (say February 01, 2009) and the job will take 100 working days, when will it finish if there is no work done over weekends? I assume you can use networkdays somehow but the final logic escapes me

View 2 Replies
View Related
May 3, 2013

In a sheet, like the example, I have monthly shift schedules for employees. What I want is to count the total shifts for Saturday and Sunday, separately Night (N) and Morning (M)/Afternoon (A) shifts, as in columns with red.

ABCDEMNOPQRAGAHAIAJ1JUNE 20132DATE1234121314151617....

HOW MANY "N" HOW MANY "M" AND "A"3SATSUNMONTUEWEDTHUFRISATSUNMON....

AT SAT-SUNAT SAT-SUN4Employee1NNNNNMM--A205Employee2MMMM-NNNNN246

Employee3AAAAM--MMM067Employee4--MM-AANNM408............................................9EXAMPLE

View 6 Replies
View Related