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 RepliesI 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.
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 RelatedIm 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)
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 ?
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...
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.
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 RelatedThe 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 RelatedMy 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?
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)
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.
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.
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
I need to set up something that "calculates" an orders next delivery date. We deliver on Tuesdays, Wednesdays, and Thursdays for different stores. IE.
Store 1 - Today's Date-Wednesday 10/28/09 = Next delivery date is Tuesday 11/3/09.
Store 2 - Today's Date-Wednesday 10/28/09 = Next delivery date is Wednesday 11/4/09.
Store 3 - Today's Date-Wednesday 10/28/09 = Next delivery date is Thursday 10/29/09.
looking for for some help on a fairly simple problem: i've attached a worksheet, and in column B (Due to Supply Chain) i'd like to insert a formula that will subtract 21 days from the date in column Z (Pub Date), and then round that date to the nearest wednesday. is this possible?
fyi: the dates in column Z are in a yyyy-mm-dd format; they don't have to remain that way.
Im working on a spreadsheet which has a column of dates formatted in "dd/mm/yy" there are always 365 days listed but these could be between any period. I am trying to create a table which looks up all mondays, tuesdays etc ....
I need a formula to find the first monday in the list and then to fill the rest of the tables. So far I manually input the monday and the rows below use =previous row + 7 to fill the following mondays, then in the next section it will refer to the monday and +1 to populate tuesday then the +7 in the rows below.
Ideally i would like a system which would log these dates automatically, the trouble is, sometimes the Thursday or another day will be first ( eg if the raw data is between 01/01/09 - 30/12/09 then the 01/01/09 is a thursday so the first thursday is 01 but the first monday will be 04/01/09.
I'm trying to account for the date and have it change if the original falls on a weekend. I wrote it using the Weekday function, which I believe is a worksheet function and not a VBA one, as I keep getting a run-time error 5 (invalid procedure, call, or argument). Either that or I have something programmed wrong in it.
View 8 Replies View RelatedI have a running schedule with mutlile tasks scheduled for different day # or the same and would like to have the dates auto fill in once I enter the start
date.
example:
4/20/06 task 1 day 1
? task 2 day 2
? task 3 day 2
? task 4 day 3
Keeping in mind that I don't want to include weekends and that I'll need to delete a range of holiday dates.
this formula in a cell. But I noticed it doesn't work if the day is friday because the next working day would be Monday.
I tried to replace the Today with weekday but I'm not doing something right.
I am receiving the data from the client , I want the serial no with the day of the date ... like this .. i ope u got it..
Like
thu01 20-Sep 2007
thu02 20-Sep 2007
thu03 20-Sep 2007
thu04 20-Sep 2007
Fri01 21 Sep 2007
Fri02 21 Sep 2007
Fri03 21 Sep 2007
Fri04 21 Sep 2007
Mon01 23 sep2007
mon02 23 sep 2007
I may receive more that 100 mails in a day .. so the serial should get updated of its own.
I want to calculate the number of weerks in a year. I use the following formula that seems to work
View 14 Replies View RelatedSubtracting 1 day when the weekday is a Thursday. I need with the formula above (on AA2):
View 2 Replies View RelatedI have a worksheet that has in column A.
Mon
Tues
Wed
Thurs
Fri
I want to insert four new rows under each weekday.
Example:
Mon
blank row
blank row
blank row
blank row
Tues
blank row
blank row
blank row
blank row
I wish I had thought of this before I created 6000 rows consisting
of:
Mon
Tues
Wed
Thurs
Fri
Repeating over and over.
This was setup to track items ordered per day but I forgot I might have to order 4 items each day in some cases.
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?
I have a list of dates in Column A, with a list of associated values in Column B. I'd like to create a small summary table that will give a count of the dates in Column A and a sum of the values in Column B, broken into a bucket for each day of the week.
I know how to create this table using a short macro that would loop through the list of dates, but if possible I'd like to calculate the values using worksheet formulas so that
I don't have to run the macro each time another set of data is added to the list.
Formula in Range("E4")
=IF(WEEKDAY(E2)=1,"CN","T" & WEEKDAY(E2))
what this in VBA?
I have a cell that is set up as an external data source that is constantly changing 24 hours per day 5 days per week. What I am trying to do is copy that cell to another worksheet and save that dymanic data as a static value and save that data every 30 minutes synced to my PC clock.
View 9 Replies View RelatedI have worked enough with vlookup and addif functions,
Now I have a different problem.
Please find attached file.
Sheet7 contains transactions numbers based on weekdays.
There are other 6 sheets representing weekdays 1 to 6.
What is the easiest way to populate the relevant transactions in relevant sheet?
For example All transaction numbers from sheet7 that relate to weekday 4, must be copied in column B of sheet "4".
I'm having problems on something that should be easy. On the basis of a date, just move to a pre-defined day-of-the-week cell. If the day is a Monday, the active cell will end up being the range set aside for Mondays.
Sub findFirstDay()
Dim workDOW As Integer
workDOW = Weekday(Range("b1"))
Select Case workDOW
Case workDOW = 1: Application.Goto reference:="sunFirstday"
Case workDOW = 2: Application.Goto reference:="monFirstDay"
Case workDOW = 3: Application.Goto reference:="tueFirstday"
Case workDOW = 4: Application.Goto reference:="wedFirstDay"
Case workDOW = 5: Application.Goto reference:="thuFirstDay"
Case workDOW = 6: Application.Goto reference:="friFirstDay"
Case workDOW = 7: Application.Goto reference:="satFirstDay"
Case Else: Application.Goto reference:="wedFirstDay"
End Select
End Sub
The only case statement that executes is the "otherwise". I've tried working with WorkDOW as String, also to no avail.