Add Days To Date According To Weekday Of Date
Aug 15, 2007
Based off of the current thread:
Display Time Depending On Weekday
I borrowed some of the formula to help rectify my problem, however I need to add certain # of days depending on what day of the week it is. Here is what I have so far.
=IF(WEEKDAY(M7)=4,M7+5,IF(WEEKDAY(M7)=2,M7+2, M7+1))
M7 is a date entered by the user, This formula is in N7.
If the date is a Monday or a Wednesday, the formula works...
If its a Tuesday, it works (M7+1).
However if the date is a:
Thursday
Friday
Saturday
Sunday
I need the formula to determine that and add the appropriate # to bring it back to that monday.
For example, person enters 16 Aug 07 (Thursday) it needs to add 4 days to bring it back to display 20 Aug 07.
View 9 Replies
ADVERTISEMENT
Apr 23, 2008
I need to create a formula that states a delivery date when the order date is entered in an adjacent column. Items ordered on Monday, Tuesday and Wednesday will be delivered the Friday of the following week, eg. ordered 23rd April 2008, delivered on the 2nd of May 2008. Items ordered on Thursday or Friday will be delivered on a Friday 2 weeks later, eg. ordered on the 24th April, delivered on the 9th of May 2008
View 4 Replies
View Related
Aug 15, 2007
Based off of the current thread: [URL]....
However I need to add certain # of days depending on what day of the week it is. Here is what I have so far.
=IF(WEEKDAY(M7)=4,M7+5,IF(WEEKDAY(M7)=2,M7+2, M7+1))
M7 is a date entered by the user, This formula is in N7.
If the date is a Monday or a Wednesday, the formula works...
If its a Tuesday, it works (M7+1).
However if the date is a:
Thursday
Friday
Saturday
Sunday
I need the formula to determine that and add the appropriate # to bring it back to that monday.
For example, person enters 16 Aug 07 (Thursday) it needs to add 4 days to bring it back to display 20 Aug 07.
I think I would run out of IF statements for this formula.
View 9 Replies
View Related
Sep 20, 2009
I have a list of clients that have specific requests waiting to be actioned, with a number of columns relating to client details and the status of the request across the top of the sheet. One of the columns (D) is "Last Contact Date", where I enter in the date that I last followed up with the client or made contact regarding their request. In the next column (E), I want the weekday/workday date 1 month after the Last Contact Date.
I can use "=D2+DAY(30)" to give me the date 30 days later, or "=DATE(YEAR(D2),MONTH(D2)+1,DAY(D2))" to get one month later, however what I want is the nearest WORKDAY after this date.
So, if the date is on a weekend, I need the Monday date instead.
View 5 Replies
View Related
Nov 22, 2013
I 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] ...........
View 7 Replies
View Related
May 25, 2014
I am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 1 Replies
View Related
Feb 19, 2010
I'm trying to do an IF function involving the date. Basically if the current day is a weekday then I want the cell value to be 30. If the current date is a weekend then I want the cell value to be 50. I'm pretty lost on how to write the formula.
View 3 Replies
View Related
Jan 29, 2008
Im trying to calculate a date in excel.
A2 = 28.01.2008
A1 should provide the date from A2 minus 1 weekday. (so 25.01.2008)
View 9 Replies
View Related
Jul 14, 2007
I am looking for excel to return a day from a date value, 14/07/07 = Saturday. Need this in VB ?
I have tried the DATE() and TODAY() etc.. Do i need to first tell excel a day by date so it can work it out, or can i do it in code???
The reason is im looking for it in VB to generate a report on a weeks data, which is inputted by a user. I will know the first date will be a range 1, but then need to convert it into a Day name ?
View 5 Replies
View Related
Sep 19, 2007
The code is ok until I hit a month with 4weeks in it and days left over.
The code puts a week total in place of a Sunday, but as some months end before a Sunday appears the code just builds a Month end total sheet.
What I need in this case is a Week total even if there is no Sunday before it builds the Month end Total.
I hope i'm being clear
Sub NewSheets()
Dim Dte As Date, Dy As Date
Dim i As Long, j As Long, Dys As Long
Dim CountWeek As Boolean
Dim Shts As Long...
View 3 Replies
View Related
Jun 5, 2008
I used a formula I found on this site to find the last friday in a month. = DATE(" & Str(iYear) & ",1+1,0)+MOD(-WEEKDAY(DATE(" & Str(iYear) & ",1+1,0),2)-2,-7)
Is there an equivelant date function that can be used when coding in vb (not within a cell.) The VBA editor does not recognize this. I believe it is because date is reserved for variables. If anyone can make this line of code work in vb so as I dont have to asign it to a cell in a worksheet you would be my hero.
View 4 Replies
View Related
Apr 8, 2009
I am trying to get the results of the number of days between today and a future date. I am using ="cell containing futuredate"-today() and it gets me the correct number of days. The problem comes in when I have yet to populate the future dates. I am getting -39991 (numeric value between today and jan 01 01) and because I am also using conditional formatting this is even more of a problem. Is there a way get excel to display nothing if it is a negative number? or to give a specified resut if the number becomes negative such as Expired or something of that nature?
View 3 Replies
View Related
May 19, 2009
I need a formula that will calculate the number of days from a date entered into cell A1 to today's date. Whether it's before or after todays date. Example:
5/10/2009 to today is -9
5/22/2009 to today is 3
View 2 Replies
View Related
Mar 19, 2012
how can i count the positive days to a inserted date value or the past days (negative value) to the system date (today).
View 5 Replies
View Related
Sep 18, 2008
I have a worksheet that has a sent date and expected delivery date I need create a macro that will alert me if today's date is within 5 days of expected delivery date.
View 14 Replies
View Related
Apr 2, 2009
I have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.
For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.
View 9 Replies
View Related
May 26, 2014
i am trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 4 Replies
View Related
Oct 22, 2007
I'm looking to display the weekday in a cell, based on the date in cell A1. I know the code I can use is
View 5 Replies
View Related
Jun 2, 2008
The D column is a date (formatted ddd d/m/yy) and the F column contains a string variable such as Red, Blue, Black (D2:D310 & H2:H310). I need a formula that can count how many times the word "Red" occurs on Monday, Tuesday, Wednesday, Thursday, Friday. I am showing the results in a table in which Q4:Q8 are the weekdays and R3:W3 are the names of the colours to be counted.
View 4 Replies
View Related
Jan 17, 2010
I have two questions regarding date format and hope you can provide input.
1) say 01/01/2010 displays as Jan-10 and i need a new column to state it as JAN. What function should i use to achieve it?
2) i need to state the difference between first day and last day of the month. What function should i use to achieve it?
View 2 Replies
View Related
May 13, 2008
I have column B with a heading "Days Remaining" and column L with a heading "Deadline". starting with row 5, I need to be able to enter a date in L5 and see the days I have left, from that day untill today, on B5. I need to then be able to enter a date into L6 and see a result in B6 and on and on. Then I need to be able to insert or delete a column and have the formulas still work in the columns with the heading "Days Remaining" and "Deadline"
View 7 Replies
View Related
Feb 18, 2014
My spreadsheet opens. Cell A1 determines what today is, formatted as "weekday, day month year" (e.g., "Tuesday, 18 February 2014").
I have another cell in which I typically enter the date for the coming Friday. Instead of updating this field once each week, I'd like to calculate the date for the next coming Friday based off the value in cell A1 (to be concise, if "today" happens to be a Friday, then it would calculate the date of next Friday). (Continuing the example above, the coming Friday would be computed as 2/21/2014. If I open the spreadsheet on 2/21, it would calculate 2/28.)
What formula or step(s) to follow to render this value automatically?
View 4 Replies
View Related
Sep 27, 2009
I've been racking my head on this one.
I need a function where for a given date will return the date of a specified weekday in the previous week.
Example: If today is Friday Sept. 25th 2009 and I want to find the date of
the Wednesday in the previous week I would need something
like DATEPREVIOUSWEEK("09/25/2009", vbWednesday)
View 9 Replies
View Related
Nov 15, 2008
I was wondering if anyone knows how I could enter a date in one cell, then another date in a second cell and in a third cell have it have it so it minuses the first date from the second and calculates the difference outputted in number of days.
Example: 04/31/08 - 04/01/08 = 30 (days)
View 2 Replies
View Related
Jul 27, 2012
ive created an excel spreadsheet with expiration dates in one row and im trying to write a code which will pull out all information in the column if the expiration date in a certain row is within 90 days of the date the excel file is accessed
View 2 Replies
View Related
Oct 29, 2009
I have todays date in cell A2. I have a range of dates in a row 4 and numbers below each date in row 6. What i want is to Sum the numbers below the dates in row 6 if the date in row 4 is with in 90 days before or after the date in cell A2.
Example......
The answer i should get is 4 since only 3 dates fall 90 days before or after todays date.
View 3 Replies
View Related
Mar 19, 2008
I've got a spreadsheet that gets its information from outside of Excel. The Date comes across as a number stored as text. I need to some how calculate the amount of days between this imported date (Settlement Date) and Today's date.
View 4 Replies
View Related
Jun 20, 2006
See attcahed code. I need to add a line to the code so date picked by the user needs to be both weekday and not a day in the past.
e.i today : 20/06/2006
if user pick a date before today or a weekend date pivot table does not update.
Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
Application. ScreenUpdating = False
If Weekday(MonthView1.Value, vbMonday) < 6 Then
With PivotTables("PivotTable1")
.PivotFields("DATE").CurrentPage = Format(MonthView1.Value, "dd/mm/yyyy")
End With
End If
Application.ScreenUpdating = True
End Sub
View 2 Replies
View Related
May 27, 2012
There are dates in column C and I need to count how many days are coming due within 90 days of each date based on the today() function but do not exceed the 90 days.
Countif Today()+90
View 5 Replies
View Related
Mar 14, 2014
I am making process TAT(Turn Around Time) which required following information. In Excel 2007.
1-Count number of days between two dates where working days are (Sun to Thursday). So required to exclude (Friday,Sat + Holidays)
A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 22
D1-Days between two dates 21
E1 To E10-Holidays
2-Count number of days between two dates where working days are (Sat to Thursday). So required to exclude (Friday + Holidays)
A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 27
D1-Days between two dates 26
E1 To E10-Holidays
Note : Any weekend (off days) dates listed in holidays should not effect the query.
View 9 Replies
View Related
