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.
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 Replies View RelatedI 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 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
I have below formula which gives me the due status.
[Code] ......
How can I modify the code so it excludes Saturday and Sunday.
My 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 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 have the following line in a macro:
Range("b22:L" & Range("J" & Rows.Count).End(xlUp).Row).AutoFilter
This selects rows based on whether they contain data and creates an autofilter of the range.
The data Im using this on will have blank cells as the bottom rows but they will contain formulas that blank the cells based on ISNA() conditions,
How do I get the above code to only select cells with visible data and ignore those that are blank but contain formulas.
what formula I can use to calculate the difference between two dates while not including weekends. For example in cell A1 I have 27-Feb-07 and in B1 I have 05-Mar-07 ; it is possible to to calculate the difference between these by not including weekends. The weekends I am refering to are Saturday and Sunday.
View 7 Replies View RelatedI just want to ask if we can create a formula that will not go to saturdays and sundays. example :
I use =today()+3 so it means if today is tuesday the result is friday, if im going to open it tomorrow the result will adjust to saturday if that happens i want the day to be automatically go back on friday, the date itself, so it means i'll just wait on friday to get the result as "monday". It just avoid saturdays and sundays,. Is that possible? By the way, format would be m/d/yyyy.
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?
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 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 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.
What calculation would I enter in a results cell if I wanted to find the delta between 2 times in date format that repersent just the business hours of 8am-5pm, therefore excluding after hours and weekends.
eg.
Date 1 Date 2 Result Time
23/07/07 8:00 24/07/07 14:55 15:55
So far I can't get the caluclation that will compensate for the after hours and week ends.
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.
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 have three columns named "name","start_date" and "end_date"
ex:
Name start_date end_date
AB 9/11/13 10:19 AM ??
CD 9/12/13 11:45 AM ??
All i need to find is,
If Name = AB, i want put start_date+3 days in the end_date column,
similarly for name = CD , start_date+7 days.
But the calculation should exculde weekends(sat and sun).
ex: For AB, start_date = 10/3/13 (3rd oct , thrusday), end_date should be start_date+3 ,so end_date = 10/6/13 but 6th october is sunday so the formula should give end_date as 10/7/13.
I am using the standard validation from the tool bar. What I am trying to do is in
check the date in a cell and if the new date is Greater and or equal to another date in another cell. The problem is I am having is when the cell that I am checking for a date is Blank the validation does not work and one can put in a date. I tried unchecking the "Ingore Blank"
Spreadsheet example attached.
I'm trying to figure out a formula to use that will exclude negative numbers. I have two dates. I'm trying to find the difference between the two dates assuming there is a date in the first column. At times there will be a situation where the date in A1 is before the date in A2 and it returns a negative number. I'd like to return a blank if there is a blank cell in column A and also return a blank if the return number is a negative.
Example:
A1 = 1/15/2013 B1= 1/10/2013 C1(formula) =IF(A1="","",(A1-B1) C1 will then reflect 5
A2 = (cell is blank) B2= 1/18/2013 C2 (formula) =IF(A2="","",(A2-B2) C2 will then reflect a blank cell
A3 = 1/5/2013 B3 = 1/15/2013 C3 (formula) =IF(A3="","",(A3-B3) C3 will then reflect -10
I'm trying to figure out a formula that would also allow C3 to reflect a blank cell since the formula returns a negative.
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've created a formula that concatenates 3 parts in VBA. column A, B, C.
- colunm C is a general info column and can be general text OR a date value.
- if its a date value it ends up being concatenated as the sytems value i.e. col A1, col B1 and col C1(16/01/2007) becomes A1+B1+C1(39098).
im using...
'//original: =IF(Q3<>"",CONCATENATE(Q3,R3,L3),"")
ws. Range("S" & irow).Formula = "=IF(Q" & irow & "<>"""",CONCATENATE(Q" & irow & ",R" & irow & ",L" & irow & "),"""")"
The red is the column that can be text or date.
-i have tried passing col C1 to a string variable but i get #name error when i refresh the code....
infoStr = ws.Range("L" & irow).Value...........
I need to enter a range of dates and have any date that falls within that period (regardless of year) highlight. I currently have it referncing two different years, but I need more range than that and I can't get it to highlight dates in July if they were dates that happened 1, 2, 3, etc. years ago.
View 5 Replies View RelatedI have two columns... 1 with a list of dates (ex. 22-Apr-07) and the other which states either a pass or fail. Ignoring the day and year, I'm trying to get the total # of references for each month, and only count those with a 'pass'ing grade.
I am having trouble entering a formula that measures how many times "Open" occurs in one column IF another column's entry is between 2 dates or within one particular month time frame. I've tried a bunch of formulas but can't seem to get anything to work!
View 9 Replies View RelatedI have an excel report that is generated in another program every week. The report is not sorted by date and it may have 100 rows of data one week and 200 rows of data another week. My excel file has formulas that refer to this original report. I need those formulas to update first, then I need the information to automatically sort by date (column D in my excel file). I have tried to setup a script to run, but it's not working. I will attach the file and the script.
Attached File: Sample w VBA.xls
I have data showing the month by month close of the Dow Jones Stock Index in column A and the date of the close in cell B1 ( Sep-06) etc, cell B2 (Aug-06). The data goes back 20 years.
I would like to use this month end data to construct a table that shows the close at the end of each completed year,so 2005 would be the last completed trading year and 2005 would appear in cell D1 with 2004 in cell a D2 etc.
Obviously the close for each year will be the closing price for the month of December in each relevant year.
How would I construct a formula to show the closing price for the end of each year in column E that gets the information from the raw data in columns A and B.
I have what I thought was a very simple task; Change all date formulas in the workbook to values. I've accomplished this, but I was wondering if someone had a better way of doing without so many loops (I used two). NOTE - Dates are on different sheets within the workbook. I've tried setting each date on each sheet to one named range. I then tried doing this
range("dateranges").values = range("datranges").value
this obviously didn't work. Anyhow, this is what I used that works, but I'm convinced that there is a better way.
Sub FinishOU()
Dim sh As Worksheet, wb1 As Workbook, r As Range, TestRange As Range
With Application
. ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
Set wb1 = ActiveWorkbook
With wb1
.Save
For Each sh In Worksheets
Set TestRange = sh.Range("A1:S4")
For Each r In TestRange.SpecialCells(xlCellTypeFormulas).........................
I have some imported data that lists the date in separate columns.
i.e.
ColumnA ColumnB ColumnC
January.......10.........2013
How do I convert this to a date by using a formula? Later I want to use the date in an array formula to sort the list.