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.
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,
I want to create a formula that works out the number of days between two dates but excludes weekends?
View 3 Replies View RelatedI 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.
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 RelatedI am attempting to get the formula in Column "E" (see attached excel file) to work based off the individual reps 'days off / holidays' (Columns K:P (or more as needed) that they might have. the formula I am using (which works fine) is:
=IF(C2=D2,0, SUMPRODUCT((WEEKDAY(ROW(INDIRECT(C2&":"&D2)),2)={2,3,4,5,6,7})*(COUNTIF($K$2:$S$2,ROW(INDIRECT(C2&":"&D2)))=0)))
My issue comes up when I am coping this formula for say 100+ rows. When I copy the formula to all of the rows, the (COUNTIF($K$2:$S$2) becomes static and does not realize that the row (ie. Row 26 Rep C) has different days off than Rep A. I can manually change the reference for the countif to specify the correct row to their own days but that becomes tedious very quickly. I was wondering if anyone has come across a way to make the countif work based off of that the name in Column B matches the name in Column G then looks 'to the right' and uses the 'days off' that are listed for the corresponding Rep. I have tried a few different ideas but nothing has worked so far.
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))
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
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.
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?
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.
Is there a way to autofill dates in excel to exclude weekends and bank holidays?
View 6 Replies View RelatedI 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 RelatedI am trying to compile a count of dates using a numerical reference which adds x consecutive dates onto the initial start date based on the number in a cell.The dates added cannot fall on a weekend.
The start date for each calculation begins in D4 with the corresponding number of days to add onto this date in K4. In the example below 8/11/2007 -- 2 means that the 8th and 9th of Nov get one added to their count. As the next row also has 9th Nov as a start date one more is added to 9th Nov. As the 10th and 11th were a weekend they are skipped and the count starts again from the next Monday. I have enclosed a simplified worksheet with some sample data.
Example:
D4 --------- K4
8/11/2007 -- 2
9/11/2007 -- 3
Expected output:
AE4---------AF4
8/11/2007 -- 1
9/11/2007 -- 2
12/11/2007 --1
13/11/2007 --1
I am creating a problem report spreadsheet for work. In col C I have a date/time that the problem started, eg, 23-Jun-2006 15:20 and in col D I have a date/time for when the problem was resolved, eg, 26-Jun-2006 15:40. I need to calculate the amount of hours that we were not available for use (due to the problem). This is based on a list of available hours. Eg, on Mondays we are available 19.5 hrs, T-F 22.5 hrs, Sa 14.5 hrs, and Sun 0 hrs.
If I do a straight subtraction, it uses 24 hrs for each day - not taking into account our available hrs. If I do NETWORKDAYS it automatically doesn't consider Sat or Sun - and i need it to - and it does have a DayEnd and DayStart but it assumes they are constants. I found the day corresponding to the date and thought I could determine the series of days between it, e.g., if the start day was Friday & the end day Monday, the series in between is Sat & Sun. Then I could do a lookup on all 4 days in a table & add them all together. But I can't figure out how to do this!
I need to calculate the number of days between 2 dates including holidays and weekends. That means I cant use NETWORKDAYS as it excludes weekend.
View 9 Replies View RelatedI 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] ...........
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 RelatedI 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 RelatedI'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?
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.
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.
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 RelatedI am trying to calculate the number of days between two dates where the arrival date is in b5 and the departure date is in c5 for each month. I have been using the following formula =IF($C5="","",MAX(0,MIN(L$1,$C5)-MAX(K$1,$B5))) but when the stay is a full year it is giving me 365 in a single column. I think maybe I need to add in the number of days in the month but not sure how to do it.
View 2 Replies View RelatedI need to calculate the number of days between a string of dates in Column A. There will be blank cells in between the dates and I need to ignore those blank cells. I'm trying to use this formula: =DATEDIF(A13,A15,"d") but it returns an error with a blank cell in the string.
View 4 Replies View RelatedMy sheet looks like this - example:
ID Dates CombinedAverageDaysFromID
0001 2012-02-01 ?
0001 2013-08-10
0001 2013-10-10
0402 2011-01-02 ?
0402 2013-01-05
0402 2013-01-22
0003 2009-02-04 ?
0003 2009-12-04
0003 2010-01-04
0003 2010-03-03
0003 2010-08-02
0003 2012-04-04
0003 2013-01-05
0003 2013-10-03
I need to calculate the average days between the dates for each ID? How do I do this?
I have a start date formatted as dd/mm/yyyy in cell A5
I have an end date formatted as dd/mm/yyyy in cell E5
In cell F5 I would like the result of the number of days between E5-A5 (Excluding weekends)
In cell G5 I would like the total number of days in the range F5:F25
In cell H5 I would like the total number of working days from A5 to Today
I am using the following formula to get the result in F5 etc
=SUM(INT((WEEKDAY(A5-{2,3,4,5,6})+E5-A5)/7))
I have copied this down and in the blank cells I get #NUM!
In cell F? were there is no date entered in cell E? I get a minus number (which I can hide using conditional formatting), but the minus number obviously has an affect on the result in G5, as cell G5 gives me #NUM! instead of the result for the current numbers in row F
i have a few fields with dates in my excel sheet. I also have a field, where i want to automatically insert the business day between two other date-fields! Business date means days without the weekend and public holiday! Is it possible to do that? how?
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.
I'm looking for the formula to calculate the number of winter days between two dates.
Say, that I'm having:
- Winter season start date (say Dec 1st)
- Winter season end date (say, March 1st)
=> All the days which are between these two are considered winter days for any given year.
- Two different dates defining the beginning and the end of my PERIOD. The total length of the period may be more than one year.
HOW do I calculate total number of winter days for the PERIOD?