I'm using excel 2010 on a pc. I need to create a calender. So far I have been using the tutorial for the pop up calender. The result I'm looking for is to set up a specific time frame and have the calender provide the "due" date. I would like the due date to exclude weekends and holidays. Would I be able to do this with the pop up calender?
View 3 RepliesMy aim is to find the end working date for each task, as well as the next working date for the next task. The working days for this employee are only Monday (6hours), Wednesday (6 hours) and Friday (8hours).
D
E
F
G
H
Start Date
Duration
Completed Days
Remaining Days
End Date
4
5/05/14
3
=IF(TODAY()>=H4, E4,IF(TODAY()
I have been asked to create a worksheet to calculate the percentage of the utilisation of a number of loan cars per month. Each of the cars may be used on each and every day of the week but the percentage utilisation is to be calculated on working days only. However it is essential to record the name of the driver of the car at weekends and holidays. Column A is populated with the dates of the month. Colum B is populated with the relative days of the week. In column C and subsequent columns I record the name of the driver of a particular car registration. Is there any way that I can count the number of drivers of each car on working days only and not weekends or bank holidays?
View 5 Replies View RelatedI'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.
I have an existing formula in a cell linking back to a start date only. determining how to add an additional formula to exclude weekends, and possibly holidays.
View 2 Replies View RelatedI would like to find out if a job took more than 24 hour cycle time (eg. 6:00 am to 5:59:59 AM next day). data:
A1 = received date & time (format "m/d/yyyy h:mm")
B1 = completed date & time (format "m/d/yyyy h:mm")
my formula is '=if(B1-A1>"24:00"+0,"Yes","No"). The problem with the formula is that it doesnt exclude weekends nor public holidays. I couldnt formulate a solution using networkdays function......
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 am trying to create a schedule that includes weekdays only. Is there a formula or set-up step to do that?
Currently I have 3 columns:
Start date Action date Finish date
The Action date column has the formula: that cell (say C4) minus number of days that action requires (say 10), but when 10 days fall on a weekend, I need to change to the next work day instead.
I have been trying to count dates in my spreadsheet. The dates are supposed to go back in time (what they do). But I want them to jump over the weekends' dates instead of landing on them.
What I have is Start Date, Req'd Qty, Days it takes to make, Date it is required to start production (which I have a problem with).
A
1 26-Nov [Thursday]
2 1500
3 5
4 'Problem' I used for this cell that formula: =if(A2>0,A1-A3,"") , as I don't want it to show anything if there is no requirements.
It comes back with 21-Nov which is Saturday when I want it to come back with 19-Nov (Thursday) as we only have 5 working days in a week.
Is there a formula similar to WORKDAY that would include weekends and make something due on the next business day? For example, I have a bill due on 6/1/11 and I need to follow up 5 days later - which would be normally 6/6/11 but the WORKDAY formula makes it 6/8/11 as it is adding 2 extra days.
I am using Excel 2010.
I got Excel for Mac 2011 installed. I have a table called 'Raw" that holds data from the beginning until the end of 2013 (in total ~7000 rows). I have columns for Category, Start and End time, and time period in Hours, Minutes, and Seconds:
Code:
A
B
C
D
E
F
Category
Start
End
Hours
Minutes
Seconds
Transportation
2013/01/01 01:00:00
2013/01/01 01:15:00
0:15:00
15:00
900
[Code] ...........
I'm trying to get an overview of how much time I've spent in January on Transportation. I would need to look at the Category entries for Transportation, then only use the ones which have the Start set to 2013/01, and then take the Hours/Minutes/Seconds, and summarise it in a new table called 'Data':
Code:
A
B
C
D
E
F
Month
Category
Occurrence
Hours
Minutes
Seconds
2013/01
Transportation
48
[Code] .......
I have figured out a way to count the occurrences for Transportation for January:
=COUNTIFS(Raw!B:B,">="&"2013/01/01 00:00:00"+0,Raw!B:B,"
I'm using Excel 2010. I need to populate a daily calendar with the number of nights spent, extracted from the Date of Arrival and Date of Departure of individuals.
View 2 Replies View RelatedI am trying to pull a specific time frame of data from worksheet, in a large file, into another active workbook. A fiscal month. I don't know how to at all. I figure it should be a And IF and Vlookup but do not know how to execute it all.
View 1 Replies View RelatedI want a drop down calender to choose date from and found that i can use More Controls> "microsoft Date and Time Picker 6.0 (SP4)" for excel 2007 but it doesn't work with other excel and shows a "x" not recognised in some other machines.
View 1 Replies View RelatedRather than manually typing the time in 5 min increments across a range of cells, i want to have a start time, then the next cell add 5 mins. EG: 09:00 next cell 09:05 and so on.
View 7 Replies View RelatedI am trying to figure out how to take holidays and weekends out of my data.
View 9 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 have a schedule of holiday dates through year 2010 off to the side in cells N8-S18 that a workable formula could refer to.
........C...................D..................... ......E.........
1......1............10/25/2005...........10/26/2005
2......2............10/27/2005...........10/29/2005
3......0............10/29/2005...........10/29/2005
4......3............10/29/2005...........11/01/2005
Column C is the duration of the project in days. Column D is the project start date. Column E is the project end date.
As example, in D4 I'm using the formula =IF(C3=0,E3+0,E3+1). I need to include something to make it ignore weekends & holidays.
I don't have much experience in multiple conditions - I tried some things & made a decent mess of it.
i have a cell with a date in it and cell with number of days in it
problem is i cant count weekends or holidays in the addistion
example b1 has 4/20/07 in it and c1 has 2 in it
i want to add c2 to b1 and compare to cell which could have a date in it or if not compare it to todays date
problem is i cant count weekends or holidays in the addition
so i would want to compare todays date to 4/24/07
if todays date is greater then the 2 cells added change color of cell
I am looking for a macros VBA where a user insert or update a data the date and time should be insert in column I and save the workbook.
Note: If the column I already have the date and time inserted before then it should give message record already have date and time.
I am using office 2010.
I need a formula to add just the time to ' date and time', ignoring weekends.
eg:
Fri 24-Aug-07 10:52 is the date and time
28:48:00 is the time
If I add the time to 'date and time', result is coming as Sat 25-Aug-07 15:40
But it should come as Mon 27-Aug-07 15:40 (hence ignoring weekend)
I have a date in a cell and need a formula that will calulate the next 4 days excluding weekends and holidays. I have search and found where one can do more complex items but not just a simple formula. I have attached an example.
View 2 Replies View RelatedIs 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 compare values from this week to last week with the exception that will overlook holidays. ie, from Monday to last Monday. But I am running into an issue if there is a holiday in the week as my formulas are looking for the value 5 days prior to the current day.
View 5 Replies View RelatedI calculate total labor hours entered into a CRM system and use it for various reports, but it's incredibly inaccurate for the following reasons:
Any labor put in between 6pm EST and 8am EST from Mon-Fri is considered "on call"All labor from Friday 6:01pm - Monday 7:59am is considered "on call"Labor entered on a company recognized holiday is considered "on call"Not every employee takes call
So, what I'm trying to do is take the total labor and pull out only the hours entered outside of the criteria above. If it were simply based on time during the week, i'd be fine, but I have no clue how to do everything else.
In the end, my new labor hours would be split into "Business Hrs. Labor" and "On Call" labor.
Using Excel 2007 Calendar Control:
I want to select a date of choice from a calendar in a field within an Excel spreadsheet.
I do not know how to do userforms and so that is why I wonder if I can add this in just a spreadsheet.
This is how far I have gotten so far: After doing the following, I have a static calendar setting in my spreadsheet like a text box showing the current date.
Developer
Insert
More Controls
Calendar Control 12.0
I have a worksheet of each month, column for each day. If today is Monday, we want to enter data for the previous business day. In this case, it will be Friday. I would like to have Friday's date (entire column if possible) to be highlighted in white bold font with maroon fill-in. So, whoever needs to enter Friday's data would know right away when he/she opens up document which column to work on. It works Monday - Thursday but not for Friday.
8/1/20138/2/20138/5/2013
ThursdayFridayMonday
Total English-Speaking Staff646464
Vacation Scheduled (hours)45.5080.0056.00
Vacation Unscheduled (hours)24.500.000.00
Sick Leave Scheduled (hours)37.5035.0025.00
Sick Leave Unscheduled (hours)73.0069.2533.75
Training Scheduled (hours)45.000.000.00
Training Unscheduled (hours)0.000.000.00
Meeting Scheduled (hours)0.000.000.00
Meeting Unscheduled (hours)0.000.000.00
Other Scheduled (hours)45.0044.0044.00
Other Unscheduled (hours)0.000.000.00
Total Leave Hours English270.50228.25158.75
Staff Leave Equivalent33.8128.5319.84
Total English-Speaking Staff Available30.1935.4744.16
find the difference between 2 dates in excel "date 1 - date 2"..... but I'm not quite sure as to how one would do it while excluding holidays!
View 8 Replies View RelatedI have below formula which gives me the due status.
[Code] ......
How can I modify the code so it excludes Saturday and Sunday.