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 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 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
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 Shifts Per Day From Monthly Report
I have table and need to take out montly total for each worker... Now... Each hours in day have own factor. (I need total hours per day but for illustration)... So when worker works day shift from 8:00 to 16:00 it's easy... 8 hours When works from 8:00 to 20:00 it's 8 hours + 4 afternoon hours When works from 20:00 to 8:00 it's 2 afternnoon hours + 8 night hours + 2 day hours Aditional problem is when day intercept holliday or sunday when that factors need to be included (if holliday is at sunday then it's like holliday). Here is some attachment: Book1.xls I've also added last day of previous month and first day of next month because of night shifts than need to be calulcated. Therefore correct number of hours is 168 and not 188. Below I calculated manually those numbers wich I want to be automated... Also.. This is table I get.. If it's easier to make it somehow else, OK by me. And any number of aditional columns is not problem...
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
Count Working Days Between 2 Dates, But Return A Zero If The 2 Dates Are The Same Day
Today I am having a very annoying problem that really has me stumped – I need to work out the lag between a Due Date and Delivered Date But as people sometimes manage to deliver on the Due Date it needs to show a zero (as in they got it in on time) but using the formula below the result is a 1 and I want a zero Can anyone help me please? I have tried putting assorted 1s in to the formula and it looks like it might work until I copy down and find that if a person delivered one day early the result shows 3 for example!
View Replies!
View Related
Auto Open Macro To Find Correct Week Tab & Day/date In 52 Week Worksheets
I am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within  but upon opening, the cell stops at WK19 & column O  rather than WK43, Column N (which changes daily). Sub Auto_Open() week(1) = "WK1" week(2) = "WK2" week(3) = "WK3" week(4) = "WK4" week(5) = "WK5" week(6) = "WK6" week(7) = "WK7" week(8) = "WK8" week(9) = "WK9" week(10) = "WK10" week(11) = "WK11" week(12) = "WK12" week(13) = "WK13" week(14) = "WK14" week(15) = "WK15" week(16) = "WK16" week(17) = "WK17" week(18) = "WK18" week(19) = "WK19" week(20) = "WK20" week(21) = "WK21" week(22) = "WK22" week(23) = "WK23" week(24) = "WK24"......................................
View Replies!
View Related
Day Of Week day Of Month List Auto Setup
What I'm after is a sheet that self generates the day of the week in column A and the day of the month in column B. I have a month long sheet where daily entries get made in the DOW row, the day of the month is a reference. I have a macro to generate a new sheet for the next month and would like to auto populate the DOW and DOM. This typically gets done on the second day of the month (data from the first day is entered on the second)
View Replies!
View Related
Month Day Day Of Week
1 2 3 4 5 6 11/1 11/2 11/3 11/4 11/5 11/6 sat sun mon tues wed thur what i am trying to do is to be able to put a date in a cell and it would fille in the month day and day of week =$D$1 =$D$1+E51 =TEXT(WEEKDAY(D6),"ddd") cells are not correct in the formulas just copied them. i got this to work in a set up xls file when used it in the real setting i get ###.
View Replies!
View Related
Find The High Price Day Of Each Week. Then Report The Median High Day
I have an interesting problem (I hope you find it interesting too). I'm trying to find the historical "high" day of the week for a particular stock I follow. I searched for several hours but was honestly unable to make any progress. Normally I do my own formulas, so other than pasting a rare macro, that's pretty much the extent of my (lack of) experience, ie please assume I am a novice....
View Replies!
View Related
Day Of The Week
I need the name of the day of the week from a date that has to be put together. This is going into a label on a UserForm. I have a named range called wbDate. It is the month & year. The day of the month is the value of a TextBox (TBDay) on a UserForm. When I tried to combine these items, I used this vToday = Month("wbDate") & " " & TBDay.Value & ", " & Year("wbDate") vToday is dimmed as variant. I am getting a type mismatch error and I'm not sure how to correct it. What I am aiming for eventually, is the name of the day of the week.
View Replies!
View Related
Evaluating The Day Of Week ?
Can somebody have a look at the workbook that I've attached. I have set up a timeshhet calculator for work but the thing is, the hourly rates of pay are dependent upon the day of the week the Operative is working, and the time of the day. We split the shifts AM & PM and the rates are decided as per the worksheet contained within the workbook "Rates". The problem I am having is how to get the timesheet to understand the days. For example, if an Op works the Sat night or Sun night, then the rate should reflect x1.5. If it's Sat/Sun daytime, then it's x1.25. Again, and as per the "Rates" sheet (see top for the rate multiplier) Mon  Fri 09:00  17:00 is flat rate/standard hrs. If the work Mon  Fri evenings, then the rate is x1.25. How can this be done?
View Replies!
View Related
Average Day Of The Week
I have a spreadsheet where column a = date, b = weekday and c= total calls. I have this array formula to get the true average for the entire month. {=average(if(isnumber(c4:c34),c4:c34))} The question is can I do something similar to get a true average adding in the day of the week? What I need is the average amount of calls per weekday.
View Replies!
View Related
Average For Each Day Of The Week
I require a function that calculates the average amount for each day of the week for a range of dates. I'm a little new at this and I though that something like this would work but it doesn't. The TEXT function doesn't accept a range so I don't know... =AVERAGEIF(,TEXT(,"dd")="01",)
View Replies!
View Related
Looping Through Day Of Week
I'm trying to set up a simple macro to loop through some number of dates on a sheet and blank out fields next to every instance of Saturday and Sunday. The only problem I'm having is actually finding the values "Saturday" and "Sunday" Given a date in this format: 01/01/2009 I can convert to "Thursday" Problem is, the vba code still "sees" 01/01/2009 I've tried copy / paste special / paste values to no avail. I've tried changing it via VBA (a few different methods, including strings, formats, etc) with no luck. Even tried referencing my date cells from another spreadsheet and pasting the values back in. Can't seem to get 01/01/2009 to be "Thursday"
View Replies!
View Related
First Day Of Each Calendar Week
I have some keys in a file that are of the form WK05, meaning that it refers to the 5th calendar week (of this year). I would like to write a function that gives me the first day of this particular week, in this case it would be 28/1/2008
View Replies!
View Related
Set Up A 6 Day Work Week
I normally use the WORKDAY function in order to create an involved scheduling program. However, I have no idea how to create a formula that would count Saturdays as a workday. I usually type in one date and then the dates kick out to an end date several months later. How do I create a formula to include Saturdays on a daily projection (e.g. A1=8/12/06, A2=8/14/06, A3=8/15/06,etc...)?
View Replies!
View Related
Finding Day Of Week With Data Above 0
I am a basic user of Excel. I have an Excel spreadsheet with data from the last 10 years. In one column it has date in dddd,mmmm,dd,yyyy format and the second column with data for the dates. I need to have Excel extract only Tuesdays with data that is a positive number (above 0). Is there a formula, or other method of finding only these results from the whole speadsheet?
View Replies!
View Related
[VBA] Day Of Week Filtering
I've been trying to write code to filter a list of date base on day of week (Mon, Tue, etc) My problem is that i can't find a way to code the filter criteria... since the date does not contain any week of day constant. i don't want to create another column in my sheet to show the week of day constant and filter this instead. Here's my not yet finished code... Sub DayOfWeek() Dim sAnswer As String Dim bWkDay As Byte Dim loMyData As Range Dim iFilterCriteria As Integer Dim Dateserial As Integer Application.ScreenUpdating = False sAnswer = InputBox("Please select day to display data (first 3 letter of the day, eg. mon)", Title:="Select day to display")
View Replies!
View Related
Convert Date Into Day Of Week
I have a form that users input the date (not just todays date) I want another cell to take that date and input the corresponding day of week. example: user inputs 03/10/09 into cell h17 I want cell A12 to automatically have the corresponding day of week (wednessday)
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
Update Day Of Week And Date Based On Month
I'm attempting to force excel to auto update the day of the week, and the date in a spreadsheet. The date isn't as important, since it can be hard coded. The only problem there is some months have 31 days, some 30, and another with 28. I've uploaded an image of the spreadsheet, and you can see in field A1 the date/year is input. I'm wanting to find a way to force the days/dates in fields 2E and 3E to update based on the month.
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
Calendar From Formula :: Nth Day Of Week For A Month And Year
I'm using this formula from [url] Nth Day Of Week For A Month And Year This formula will return will return the date of Nth dayofweek for a given month and year. For example, it will return 26March98 for the 4th Thursday of March, 1998. Daysofweek range from 1 to 7, with Sunday = 1 and Saturday = 7. =DATE(Yr,Mon,1+((Nth(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+ (DoWWEEKDAY(DATE(Yr,Mon,1)))) Where Yr, Mon, Nth, and DoW are cell references or values indicating Year, Month, Nth, and DayOfWeek. I would like to be able to change the year and month in A2 and B2 and have the calendar change. I will be inserting rows between the weeks to return appts, if I can get this part working. I could make a new tab for each month, but I thought I would give this a try....
View Replies!
View Related
Count Formula Spreadsheet That Is Being Used For Tracking Work Completed Each Day In A Week
I have a spreadsheet that is being used for tracking work completed each day in a week. Each day has 5 columns and 10 rows of data to potentially be entered, some of the data is text and some is numerical. Can anyone help me come up with a formula that will count the text and the numerical entries for each day? I tried to use a nested if statement but it exceeded the number of nestings available. The range of cells for the first group is B6:F16. As a side question, will count work for a range of data or only one column or row at a time?
View Replies!
View Related
Start Day Building Out 365 Days
I have Sheet 1 with a series of 10 lists. Sheet 2 will display the information on Sheet 1 based on the day. For example, the user will input a start date. Excel should display item 1 in lists 110 on day 1 in the respective column. Then item 2 in lists 110 on day 2, etc. When it reaches the end of a list it should start at the beginning. Day 1 shold be the start date. Day 2 shold be the next day, etc. I have lists 110 all ready to go, but I am having trouble figuring out how to go about populating the other columns.
View Replies!
View Related
