Date Of Last Weekday In Month
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
ADVERTISEMENT
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
Feb 9, 2009
I have a sheet that contains a number of documents entered into a system by a user.
Each month those number of documents are average by the number of working days in a month.
I'm using this formula, =AVERAGE(C4,22)
I would have to edit this for each month with the number of working days.
Is there a way I can have this formula automatically find the number of working days in a month specificed and average it out?
View 9 Replies
View Related
Nov 18, 2013
How do I auto fill a series of 2014 dates in the row below the weekdays? I have a row C5 that has a series of auto fill weekdays successfully for 2014. I can't seem to auto fill the weekdates in the row below. I have to make manual adjustments for each Monday, and for the correct month ends, etc.
View 8 Replies
View Related
May 3, 2007
I know there's a pretty compley formula out there that counts the occurence of say Fridays in 2009 - does anyone have this? I had it before in a file but ranged valued the results showing the count of each day of the week for each month in a given year.
View 9 Replies
View Related
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
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
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
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
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
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
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
Jan 28, 2010
I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year
Column A is for date input and the same date can be repeated several tumes :-
1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09
Sometimes there are all 30 /31 days but normally not .
I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.
View 9 Replies
View Related
Dec 25, 2013
Need to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.
View 10 Replies
View Related
Mar 20, 2009
I have log data in two columns:
Column A: Date/time (at 30 minute intervals)
Column B: Numeric data
On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.
The end of the range is determined by the month in the current row.
I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.
I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.
Manual
=IF(OR(MONTH(A1009)=A4)*(A$4:A$65536
View 9 Replies
View Related
Jul 28, 2008
I have a range of dates from 2003 to 2012. I formatted them to the 'Mar-01' option, but when I want to pivot on the month, Excel still reads them as the date - example 3/25/2008, 3/28/2008...and so my pivot table has multiple columns for all of the dates present in that month.
How do I truly format my dates so that excel reads them as the month only so that I can then pivot and show 12 columns (months) per year?
View 9 Replies
View Related
May 9, 2006
=CHOOSE(WEEKDAY("1 Jan " &$C$1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
where C1 = the year 2006. B1 has the month Jan and I want to input that into my formula, so when I change the month, it automatically will change my formula. I tried =CHOOSE(WEEKDAY("1 $B$1 " &$C$1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
B1 = Jan
C1 = 2006
View 7 Replies
View Related
Jan 8, 2014
How to correct this formula to get correct output.
=IF(P9=TODAY(),IF(P9<>"",CHOOSE(MONTH(P9),
"January","February","March","April","May","June","July","August","September","October","November","December"),""))
It's working fine if the year is 2014 but not if the year is different.
Conditions:
If the date is today then only the month should display else blank. The year should be ignore.
For example:
08/01/2000: January
01/01/2000: blank cell
08/01/2014: January
01/01/2014: blank cell
The date is in column 'p' in dd mm yyyy format. In short if the date is current date that is for today '8' then only the month should be display in the output.
View 10 Replies
View Related
Aug 24, 2009
I have a problem calculating something that happened last month if the month is january. At the moment, if the month is not January it works:
View 3 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
Aug 14, 2012
I have a input box that prompts a user to enter a date of a new month - it has to be the 1st of a new month. I have validation that it is a date that has been entered but then i want to validate the date entered is a month ahead of a date in a cell range on a sheet.
It is a monthly reset so it has to roll on from the previous month.
Here is what i have currently but it isn't working.
Code:
' Get user to input the first day of the new month to populate all dates with
dNewMonth = InputBox(Prompt:="Enter first Day of the new Month. Must be the 1st of the Month e.g. 01/10/2012", _
Title:="Enter Date")
' Validates the entered date is a valid date
If (IsDate(dNewMonth) = False) Then
[Code]...
View 1 Replies
View Related
Jan 2, 2014
I have a table that looks like this (its basically a historical data of a stock exchange):
Date
Index
January 4, 2010
[Code]....
The List continues till the current Date.
I want to calculate Average Index Values of a Date of each month within a Date Range.
Example: Calculate Average Index Values for 3rd of Each month from 1st Feb 2010 to 3rd Jan 2011. Formula should calculate Average of the Index Values for 3rd Feb 2010, 3rd March 2010, 3rd April 2010, 3rd May 2010, 3rd June 2010, 3rd July 2010, 3rd Aug 2010, 3rd Sept 2010, 3rd Oct 2010, 3rd Nov 2010, 3rd Dec 2010, 3rd Jan 2011.
Both the Date and the Date Range is variable. Also, the Index Value for selected Date of one or more month may not be available as that being a holiday. In that case, the formula needs to use the last available Index Value before that Date. e.g. If Index Value for 3rd Oct 2010 is not available, system will use the Index Value of 2nd Oct 2010.
View 1 Replies
View Related
Jul 28, 2009
Serial No Search E220060926320061125420060612520070824620061026720061226820061127920061226 Excel tables to the web >> Excel Jeanie HTML 4
E - Year Month Date
I need F column as Month Date Year Format
View 9 Replies
View Related
Oct 25, 2006
I have a spreadsheet which I use to track when a work request is recieved, when we confirm the request and when we action the request. I have been trying to write some code to count the amount of requests, receipts and actions we have processed in the last month.
My first column shows who the request is from
The second shows date recieved
The third shows date we send receipt
The fourth shows the date actioned.
View 9 Replies
View Related
Dec 12, 2013
I'm sent a file every day where column A contains a month & year but in "general" format.
For example;
Column A, starting with cell A1 with the next date in A2, etc
Jan15
Feb15
Mar15
Apr15
Is there a way through a formula or macro that I can use in order to run through all the dates in this column to make sure that the next cell down is always the month after the cell above it? The dates go all the way through to 2018 in the above format.
View 3 Replies
View Related
