Calculate Days In A (06 Day)Working Week
to calculate no.s of workdays between two given dates,
1.We work 06 days a week (so only Sunday is OFF)
2.What if I have some holiday between the given dates + Sunday OFF
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Calculate Working Days Function
I have just built a spreadsheet to calculate training hours amongst other things. I have used the function NETWORKDAYS which calculates the number of working days between two dates. This works fine on my laptop which has the analysis tools pack installed. Unfortunately my work place IS policy wont allow the analysis pack to be installed so need to find away around this. Does anyone know a formula that will perform the same function as the NETWORKDAYS. THis is crucial to the accurate calculation of training hours.
View Replies!
View Related
Calculate Working Days Left In Current Month
Is there a formula to calculate the working days left in the current month? I work in the financial services industry and am putting together a spreadsheet to automatically calculate sales production needs per "working day", based on my businesses schedule. We are open MF and Saturday, not including holidays. Now, I can look at a calendar, count the days left and put them in to the spreadsheet, but I'd prefer to have it calculate automatically. On my spreadsheet, I have used the =TODAY() formula. In the cell below, I would like to have it calculate the actual working days remaining in the current month. Since today is Sunday August 19th, I know that there are 11 working days left (starting Monday 08202007). Is there a way to get Excel to do that?
View Replies!
View Related
Weeknum Says There Only 2 Days In Week
Why does Weeknum say there are ONLY 2 days in the first week of 2011? Aren't there 7 days in a week? =Weeknum("112011",2) = Week 1 =Weeknum("122011",2) = Week 1 =Weeknum("132011",2) = Week 2 (I'm assuming the first day of the week is a Monday, but I still get wacky results if I start the weeks on a Sunday.)
View Replies!
View Related
Production Days In A Week
I have a production schedule with start and finish dates for each project. To the right of this I have a matrix with "week starting" dates. What I am looking for is a formula that will indicate how many days in each week the project will take. Below are the cells filled in correctly. However, I did this manually. Any idea of a formula? I'm pretty new to this. WEEK STARTINGSTARTFINISHUNITS PER DAY9/1/089/8/089/15/089/22/089/29/089/10/089/24/0813.4503530
View Replies!
View Related
Filter Days Of The Week
How could I filter days of the week. Although the date formatting contains the date, ie Thursday, 18 October 2007, when I try to use a contains filter it wont recognise the text of the day I want to filter.
View Replies!
View Related
Counting Days Of The Week
I need to be able to count the number of each day that has passed so far this month. For example, so far this month the table would look like: Mondays:1 Tuesdays:1 Wednesdays:1 Thursdays:1 Fridays:0 Saturdays:0 Sundays:0 I don't want the count to include the current day. My data is in a workbook called "Stock Today.xls" on a sheet called "Sales" in column S.
View Replies!
View Related
List All Days For The Week For The Year
i have the year in cell A1 (Just The Year) what i was looking for is a formula that will list all the days of the week.. IE if i pick Monday form a drop down list than it would like all the mondays for all twelve (12) Months so Jan Column would say 5 12 19 26 31 and so on for each month than if i picked a different day it would do the same Using Microsoft Excel 2003 On Windows Xp Home Edition
View Replies!
View Related
Build Array With Days Of The Week
This line will give me the current weeknumber wkNumber = DatePart("ww", Now(), vbMonday, vbFirstFourDays) But also i would like to have a dynamic array that will give me the dates of this week Currently i do that using vlookup in a table stored in my personal.xls but that is no longer possible as i have to distribute my macro and i no longer have that possibility.
View Replies!
View Related
Format Color For Some Days Of Week
I have a Timesheet where I am trying to get Saturday and Sunday to auto fill with a certain color whenever the month is changed. The spreadsheet is setup so that if the month is changed in a specified cell, then the dates of that month auto fill down a specified column. I would like Saturday's and Sunday's to be a certain color without having to manually color fill them every time the month is changed. Is there a formula or macro for this. I have tried setting up conditional formatting but I don't know how to get the color to follow Sat&Sun. I have attached the spreadsheet.
View Replies!
View Related
Year,month,week,days Calculation
What I am trying to to is calculate the number of Years, Months, Weeks, and Days from one date to another. So far I can calculate years and months accuretly but I'm having trouble with the days and can't seem to figure out how to do the weeks. I'm using the formula: =DATEDIF(B1,B2,"y")&" Year(s), "&MOD(DATEDIF(B1,B2,"m"),12)&" Month(s), and "&(MOD(DATEDIF(B1,B2,"d"),365))&" day(s)" B1 is the current days date B2 is the entered date What I am getting when I enter the date 9/14/09 with the current date (8/3/06) is 3 years, 1 month and 43 days. when it shoud only be 3 years 1 month and 11 days. Any date I enter the days are not right. I can't seem to figure out what I am doing wrong. Also I'd like to get the weeks to come up also. Such as Start date 8/3/06 Entered date 10/19/09. What I want to see: 3 year(s), 2 month(s), 2 week(s), 2 day(s).
View Replies!
View Related
Calculate Sum By Week
I have a series of data in different columns, one of them is the date (daily, for like 5 years, exept weekends and national holidays), but I want to make another table with weekly data (which should be the sum of the daily data in a week, for each column). I thought there might exist any combination of formulas for that, to put once and drag it, and maybe it's pretty obvious, but even though I thought about it a lot, I couldn't find the solution.
View Replies!
View Related
Working Out The Number Of Days A Job Has Taken.
I have a table with 2 date columns, and I need to subtract one date from another to give me the number of days the job has taken, but this also needs to exclude the weekends. There is no hours involved. I.e. Start Date Finish Date Days Taken 22/Jan/2009 27/Jan/2009 4 Is there a formula that i can use to work out the days taken. there is multiple rows of information, the above is an example.
View Replies!
View Related
Number Of Working Days Between 2 Dates
I'm currently working on a file that has employee holidays/sickness etc. What I want to do is be able to see is a DATE FROM and DATE TO and next to it the total amount of working days between and including those dates. On top of the since employee's sometimes like to take half days I need it so that it can determine half days as well. So for example I have an employee going to be off work from 29/10/2007 until 09/11/2007 12pm which is 10.5 days in total, but that includes weekends when I don't want it to. Also it comes out as 11 days at the moment because I can't figure out how to get it to determine is it is half a day. What formula should I use or if someone could create an example that would be really usefull :D
View Replies!
View Related
Adding 7 Working Days To A Date
I have a worksheet where in column B I have a vendor name, column E I have a date value and in column AF I need to calculate a due date based on 7 working days for vendor A and 14 days (not working days) for vendor B. I've tried to write an IF statement but can't do it. Can anyone suggest the right combination of IF/AND statements that would do this? I attach the workbook should my useless attempt at explaining my problem be unclear...
View Replies!
View Related
Calculate Week Number From Sheetname
Daily i import sheets into excel and the sheet name is uniformed to the following 20061017_BNKREC  20061018_BNKREC  20061019_BNKREC .......... just for clarity purposes [2006] = year, [10] = month, [17] = previous day, [_BNKREC] = report type I'll be creating a graph to which shows account balance by week, by account. The data will be coming in daily. i know i will need to create either a dynamic range or copy my data into a new sheet. My head is spinning because i need excel to somehow (either in a formula or VB) determine what WorkingWeek the sheet is in. I dont want to have to keep adjusting formulae or ranges when import a new sheet.. bare with me here as its hard to explain ................
View Replies!
View Related
Excel 2007 Net Working Days Function
I am trying to use the NETWORKDAYS function in excel to calculate the working days for my company. the problem is I m located in UAE and here Saturdays and Fridays are official days off for my company. Now the problem is that the excel have this built in function that Sunday and Saturdays are weekend days. I want to customize the function for my local days off for my company.
View Replies!
View Related
Insert The Working Days Of The Current Month In A Row
I have an excel sheet where I need to insert the working days of the current month in a row. The working days are from Sunday through Thursday, that is, 5 days a week. For example, for the month of July, 01 is Tuesday and the week ends on 3rd. So the row should look like this: 13  610  1317  2024  2731
View Replies!
View Related
Conditional Formatting For The Number Of Working Days
I am trying to do a conditional formatting for the number of working days. What I am trying to do is: Example: As in the row D7 I give the actual workdays required to finish the task the Gantt Chart gets a specific formatting on the right appears for the number of working days. Problem: I want to avoid the formatting if there are any weekend (Saturday or sunday) Suppose I give number of working days as 20 then there would be 6 weekend days so it shall add to the overall actual working days but again sinnce we are adding 6 additional days one more weekend day would come in between so the formatting shall take care of this.
View Replies!
View Related
Pull Forward The Dates By 3 Or 5 Working Days
I have a spreadsheet stating a date that my company will deliver a product by but due to a set of certain circumstances we need to pull forward the dates by 3 or 5 working days. Basically it needs to show the dates below but minus 3 days unless this includes nonworking days (i.e. weekends) 01/01/10 02/01/10 03/01/10 04/01/10 05/01/10 06/01/10 07/01/10 08/01/10 09/01/10 10/01/10
View Replies!
View Related
Calculating Working Days For Multiple People
I'm using Excel 2003, and successfully working out start date (T2 in the formula below) plus duration in days (U2) minus any nonworking days listed in a seperate worksheet. =WORKDAY(T2,U2,Holidays!C6:C17) Each 'task' is on a seperate row, and the lead officer is named in Column E. How can I get the work day function to include the nonworking days for each officer as well as the global nonworking days in the seperate worksheet? I thought about using a vlookup, but that only matches the first nonworking day for each officer rather than all of the nonworking days.
View Replies!
View Related
Calculate Work Hour Phone Time By Day, Week & Month
I am trying to figure out a way to count the minutes used from 8am5pm MondayFriday. I have this years worth of data. I would like to do it by month and by week and by day. I use office 08 for the mac and its my understanding that it doesn't have VBA. I would also like to be able to figure out if on a certain date a employee made over x amount of phone calls in a day. But have several employees. I have columns that are labeled date, employee, minutes used.
View Replies!
View Related
Calculate Sick Days
I’ve tried formulas for this but nothing is working, and was wondering if there was some vba that would do the job Please find attached a sample sheet of where I’m up to. I’m trying to calculate when someone goes sick to see whether they are entitled to company sick pay (CSP) Statutory sick pay (SSP) and most people will have waiting days (W D) before qualifying for CSP. A6:A735 have set dates for two years D6:D735 have the sick days an employee has taken B6:B735 Matches the sick date to the date in column A G2 has how many days entitlement for CSP H2 has how many waiting days (W D) before CSP can start In Column C i need CSP, SSP, or W D next to the number 1 And if someone only has 1 day sick they would not qualify for any of the above and I just need “S” in column C.
View Replies!
View Related
Calculate Number Of Days Between 2 Dates
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 E5A5 (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})+E5A5)/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
View Replies!
View Related
Calculate Work Days Between 2 Dates
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 datefields! Business date means days without the weekend and public holiday! Is it possible to do that? how?
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 The Time Between Two Points (on Different Days)
I am trying to calculate the duration between two times on different days. For example  I want to know the number of days/hours/minutes/seconds between 25th Feb 2009 20:00 & 27th Feb 2009 22:00. I am sure that the key is how I enter the data into the cells and how those cells are formatted, I just cannot get Excel to see that the times are on different days. At the moment my data is in a raw format with times in one column and dates in another.
View Replies!
View Related
Formula To Calculate Sick Days
I am trying to put a time sheet together that will place the number 8 in a cell when the work "sick" or vacation is in another cell. For example, if I am out sick on monday, I would like to put "sick" in the cell next to monday and have the number 8 be placed in another place. Then If I am out "sick for three days I would like all of those "sick" days to add up to 24.
View Replies!
View Related
Calculate Days Between Subtotaled List
In the attached workbook, I have subtotaled a list of parts. I want to add a formula beside the subtotals, under Column C, "Days BO", that subtracts earliest date in column R from the latest date in column R, for each part number. I have added a simple formula to the attached example that does what I want, but the full sheet will be thousands of lines weekly, with varying part numbers, and different counts. I don't know how to take the count function into account so I can copy a single formula all the way down the sheet.
View Replies!
View Related
Calculate Days Between A String Of Dates
I 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 Replies!
View Related
Calculate Times That Span 2 Days
I have formated my cells as custom hh:mm and want to have a 24 hour clock calculate time. In cell D6 I have starting time of 23:00 In cell E6 I have finish time of 07:00 This should equal 7:00 hours but I get ######### am I doing somthing wrong or do I have to format the cells differently. The formula in F6 is E6D6.
View Replies!
View Related
Calculate Number Of Days Using 2 Sets Of Dates
I'm new here. I've searched these forums in the past for help, but never had to post, i've always been able to find something by searching. However, this time I couldn't find anything that addressed my specific need so I figured it'd be worth a shot. Alright, I have two sets of dates, and i'm trying to set up a formular that calculates the numbers of days from the first date range that overlap/fall within the second date range. For example: In A3 type "1/1/2006" In B3 type "1/1/2007" In C1 type "6/30/2005" In C2 type "6/30/2006" Now, in C3 i'd like to insert a forumla that tells me how many days in the date range of 1/1/20061/1/2007 fall in the date range of 6/30/20056/30/2006. Basically for this is should be half the year, however many days that is. Is this possible with a formula?
View Replies!
View Related
Formula To Calculate Years Months And Days
I am using the following formula to calculate years months and days in Excell 2007 =DATEDIF(C7,D7,"y") & " yrs, " & DATEDIF(C7,D7,"ym") & " mths, " & DATEDIF(C7,D7,"md") & " days" C7 3/24/04 D7 1/4/08 What is returned is 3 years, 9 months, 136 days How might if fix it show the correct days?
View Replies!
View Related
Calculate Day Difference Between Dates Excluding Certain Days
I 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.
View Replies!
View Related
Auto Calculate Not Working
I import a file and paste as CSV. I believe everything is considered text then, but there are numbers as well. The worksheet has over 4000 lines. I try to concantenate two cells (ie. a2 and b2). I use the function, but the result shows the formula. What I want is the new value or the string of words. I have checked my options and the auto calculate is turned on. I even try the various F9 keys.
View Replies!
View Related
Calculate Working Hours
i had a sheet with the employee name,Working Place, Designation,Starting Time & Ending Time of each employee working in a Department. I want to track the no of hours each employee worked in the Format 1 Hr: 45 Mins . i tried to get the result by substracting the ending time from starting time and then formatted the cells using the TIME FORMAT from the FORMAT Menu>Format Cells > TIME. But i m not getting the required result. I want a vba code or formula for extracting the no of Hrs & Mins the employee worked using the above formula.
View Replies!
View Related
Formula Required To Calculate Number Of Days Remaining
A1, B1, C1 and D4 are cell in Excel worksheet and values are 2, 4 and 6 in A1, b1 and c1 respectively. A1 B1 C1 D4 2 4 6 C1Formula is: =sum(A1+B1) Now I have to write formula for D4cell: i.e. =sum(C1/Total days remaining of the month) Suppose Today is April 01, 2009, C1/should be devided by 30, Tomorrow will be April 02, 2009, C1/should be devided by 29.
View Replies!
View Related
Formula Calculate The Number Of Days Since An Employee Worked
I am trying to calculate the number of days since an employee worked. Column A has the date and columns B,C, and D show the name of the 3 employees who worked that day. Each row shows the next day in column A with the three employees who worked that day in columns B,C, and D. I need the format of the excel sheet to remain the same. I'm looking for a formula that will calculate the number of days since each employee has worked...there are 10 different employees and only 3 work each day.
View Replies!
View Related
