Insert Row By Date

I need help with a formula. I have a certain list of dates, by day...and I need them listed by "week ending" (where a week "ends" on a Sunday).

For example, for the week ending 5/23, I might have the dates:
5/23/10 (Sunday) - the formula should pull 5/23/10
5/22/10 (Saturday) - the formula should pull 5/23/10

And if the date was earlier...say 5/11/10...it should return "5/16/10" because that is the Sunday of the week that contained 5/11/10.

I know there is a way to do this with the Weekdays function combined in a formula, but I don't remember what it is. Does anyone know?

Thanks!

SundayST

Background : I have a crosstab query that I use to collect data from other tables. I prompt the user for a date, and the user must enter a date that starts on a Monday. There are reasons for this, but the user must enter a Monday date. Once it is entered, I have the query assign numbers to the day of the week, which works perfect for Monday through Saturday, as it assigns Monday as #1, Tuesday as #2, Wednesday as #3. Once it gets to Sunday, it assigns a zero for that day. It messes up my query by assigning zero. How do I get it assign the #7 for Sunday?? This query only collects data for one week -- Monday thru Sunday, and the date the user enters, must start on Monday.

I use the following codes in the cross tab query:
1) in criteria in one column, I use : Between [MONDAY DATE] And Date()-1
2) to assign day of week : Day of Week: (Weekday(PERF_SCHD))-1

As I stated above, this works, except for Sunday, when it assigns a zero. How do I get a seven assigned to Sunday?

I would appreciate any help.

Thanks.

Dear Forum,

Placing the date (1-JAN-2008) in the right column for DAY

Now I have a Matrix in which I have the Days as Colum Headings and the Week Numbers as the Rows.

I will be mentioning the Date let's say 1-JAN-2008 in the Cell A1 and I want this date to appear in the approriate Column for the DAY
For Ex:-

The day for the date 1-Jan-08 was a Tuesday, therefore the date should appear in the Column having a Heading Tuesday and in the Column Monday it should show as 31-Dec-07 and the Columns for Wednesday,Thursday,Friday,Saturday and Sunday the Following dates after 1-Jan-08 (i.e. 2-Jan-08, 3-Jan-08, 4-Jan-08, 5-Jan-08, 6-Jan-08).

The appearance of the Date should change as per the day-date combination and as such for the preceding as well as following dates..

Ex:- Lets say for 1-Jan-09 which comes on a Thursday it should display (1-Jan-09) under column Thu and Mon,Tue,Wed should display ( 29-Dec-08,30-Dec-08,31-Dec-08) and Friday,Saturday and Sunday the Following dates after 1-Jan-08 (i.e. 2-Jan-09, 3-Jan-09, 4-Jan-09).

I am trying to get a formula that I can use to calculate the start and end dates of our fiscal weeks at the company I am working for. We recently changed our fiscal calendar, and now instead of having a fiscal week go from Sunday - Saturday, we go from Saturday - Friday.

So, I have a workbook with every day listed since 2004 in column A, and I want to calculate the week start date in column M, and the end date in column N. I tried using this formula to get the week start date based on the value in column A:

Code:

=(A2-1)-WEEKDAY(A2,1)+1

which works only for some days during the week...if it is a Saturday it calculates the previous week's start date. For Example, if I have 1/3/04 in A2, it calculates 12/27/03 as the start date, even though 1/3/04 is the start date of the week. And 1/10/04, which is the start of the next week, calculates as 1/3/04.

Any help with this? I am sure that I am just over thinking it.

This is my code for a button. Its part of a userform. I put a date in one textbox, click the the button and the other textbox tells me what the date minus 28 days is. I need to somehow have it let me know if it falls on a Saturday or Sunday. Any suggestions? Thank you.

Code:

Private Sub CommandButton5_Click()
Dim DT As Date: Dim DTT As Date
DT = Menu.TextBox7.Text 'UserformDate
DTT = DT - 28
Menu.TextBox8 = DTT
End Sub

Hi,

i have a log file where people will be inserting date (dd/mm/yyyy HH:mm) in column B, and column A is supposed to have the week number of the date in column B

problem is, i`m from Egypt, first day of the week is saturday, but all i could find was how to make monday/sunday the first day of week

how can i make saturday first day of week, and let the column A read the right week number of date in column B

Please help!

I need some assistance with adding rows to an exsiting table. I'd like the code to add a row for Saturday & a row for Sunday at the end of each week, in the below table. See table A & B.

One suggested to add the rows at the bottom and then sort the table by date. Any and All help is appreciated. Thanks

Table A
A B C D E 1 Account ID # Date Received Date Needed Date Completed 2 Wall Street Tower 1 1-Aug 4-Aug 4-Aug 3 Moonstar Investments 2 5-Aug 7-Aug 6-Aug 4 Moonstar Investments 3 7-Aug 7-Aug 7-Aug 5 Collins Machine 4 8-Aug 22-Aug 21-Aug 6 2020 Lawrence Street 5 11-Aug 13-Aug 12-Aug 7 The Cypress Club 6 11-Aug 11-Aug 11-Aug 8 The Cypress Club 7 11-Aug 11-Aug 11-Aug 9 Nittany 8 12-Aug 12-Aug 12-Aug 10 SRC Tec 9 12-Aug 19-Aug 18-Aug 11 Nittany 10 12-Aug 12-Aug 12-Aug 12 SRC Tec 11 12-Aug 19-Aug 18-Aug 13 2020 Lawrence Street 12 13-Aug 13-Aug 13-Aug 14 2020 Lawrence Street 13 13-Aug 13-Aug 13-Aug 15 Jaydyn Court at Mayfair 14 15-Aug 18-Aug 18-Aug 16 Legend Construction 15 15-Aug 18-Aug 18-Aug 17 Jaydyn Court at Mayfair 16 15-Aug 18-Aug 18-Aug


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
[/code]

TABLE B DESIRED OUTPUT
Code:

Sheet2



 

A

B

C

D

E

1

Account

ID #

Date Received

Date Needed

Date Completed

2

Wall Street Tower

1

1-Aug

4-Aug

4-Aug

3

SATURDAY

2

2-Aug

 

 

4

SUNDAY

3

3-Aug

 

 

5

Moonstar Investments

4

5-Aug

7-Aug

6-Aug

6

Moonstar Investments

5

7-Aug

7-Aug

7-Aug

7

Collins Machine

6

8-Aug

22-Aug

21-Aug

8

SATURDAY

7

9-Aug

 

 

9

SUNDAY

8

10-Aug

 

 

10

2020 Lawrence Street

9

11-Aug

13-Aug

12-Aug

11

The Cypress Club

10

11-Aug

11-Aug

11-Aug

12

The Cypress Club

11

11-Aug

11-Aug

11-Aug

13

Nittany

12

12-Aug

12-Aug

12-Aug

14

SRC Tec

13

12-Aug

19-Aug

18-Aug

15

Nittany

14

12-Aug

12-Aug

12-Aug

16

SRC Tec

15

12-Aug

19-Aug

18-Aug

17

2020 Lawrence Street

16

13-Aug

13-Aug

13-Aug

18

2020 Lawrence Street

17

13-Aug

13-Aug

13-Aug

19

Jaydyn Court at Mayfair

18

15-Aug

18-Aug

18-Aug

20

Legend Construction

19

15-Aug

18-Aug

18-Aug

21

Jaydyn Court at Mayfair

20

15-Aug

18-Aug

18-Aug

22

SATURDAY

21

16-Aug

 

 

23

SUNDAY

22

17-Aug

 

 



  Excel tables to the web >>   http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Hi,

I'm trying to set a spreadsheet so that I enter a date in one box, and it give's me 91 days from that date in another, but only counting WORKINGDAYS (So not weekends).

Is this possible? Here is the formula I'm using

=DATE(YEAR(A3),MONTH(A3),DAY(A3)+91)

This works fine for adding on 91 days, but does not take account the working days, so will sometimes return me a Saturday/Sunday... If 91 days fro mnthe date is a Saturday or Sunday, I want it to return with Friday's date...

Thanks.

I'm using this formula from Quote:

http://www.cpearson.com/excel/DateTimeWS.htm

Quote:

Nth Day Of Week For A Month And Year

This formula will return will return the date of Nth day-of-week for a given month and year. For example, it will return 26-March-98 for the 4th Thursday of March, 1998. Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7.

=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+
(DoW-WEEKDAY(DATE(Yr,Mon,1))))

Where Yr, Mon, Nth, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week.

I would like to be able to change the year and month in A2 and B2 and have the calendar change.
I will be inserting rows between the weeks to return appts, if I can get this part working.
I could make a new tab for each month, but I thought I would give this a try.

Sheet1

* A B C D E F G H 1 year month * * * * * * 2 2008 10 * * * * * * 3 * * * * * * * * 4 * Sunday Monday Tuesday Wednesday Thursday Friday Saturday 5 * 1 2 3 4 5 6 7 6 1 10/5/2008 10/6/2008 10/7/2008 10/1/2008 10/2/2008 10/3/2008 10/4/2008 7 2 10/12/2008 10/13/2008 10/14/2008 10/8/2008 10/9/2008 10/10/2008 10/11/2008 8 3 10/19/2008 10/20/2008 10/21/2008 10/15/2008 10/16/2008 10/17/2008 10/18/2008 9 4 10/26/2008 10/27/2008 10/28/2008 10/22/2008 10/23/2008 10/24/2008 10/25/2008 10 5 11/2/2008 11/3/2008 11/4/2008 10/29/2008 10/30/2008 10/31/2008 11/1/2008
Spreadsheet Formulas Cell Formula B6 =DATE($A$2,$B$2,1+(($A6-(B$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(B$5-WEEKDAY(DATE($A$2,$B$2,1)))) C6 =DATE($A$2,$B$2,1+(($A6-(C$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(C$5-WEEKDAY(DATE($A$2,$B$2,1)))) D6 =DATE($A$2,$B$2,1+(($A6-(D$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(D$5-WEEKDAY(DATE($A$2,$B$2,1)))) E6 =DATE($A$2,$B$2,1+(($A6-(E$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(E$5-WEEKDAY(DATE($A$2,$B$2,1)))) F6 =DATE($A$2,$B$2,1+(($A6-(F$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(F$5-WEEKDAY(DATE($A$2,$B$2,1)))) G6 =DATE($A$2,$B$2,1+(($A6-(G$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(G$5-WEEKDAY(DATE($A$2,$B$2,1)))) H6 =DATE($A$2,$B$2,1+(($A6-(H$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(H$5-WEEKDAY(DATE($A$2,$B$2,1)))) B7 =DATE($A$2,$B$2,1+(($A7-(B$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(B$5-WEEKDAY(DATE($A$2,$B$2,1)))) C7 =DATE($A$2,$B$2,1+(($A7-(C$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(C$5-WEEKDAY(DATE($A$2,$B$2,1)))) D7 =DATE($A$2,$B$2,1+(($A7-(D$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(D$5-WEEKDAY(DATE($A$2,$B$2,1)))) E7 =DATE($A$2,$B$2,1+(($A7-(E$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(E$5-WEEKDAY(DATE($A$2,$B$2,1)))) F7 =DATE($A$2,$B$2,1+(($A7-(F$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(F$5-WEEKDAY(DATE($A$2,$B$2,1)))) G7 =DATE($A$2,$B$2,1+(($A7-(G$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(G$5-WEEKDAY(DATE($A$2,$B$2,1)))) H7 =DATE($A$2,$B$2,1+(($A7-(H$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(H$5-WEEKDAY(DATE($A$2,$B$2,1)))) B8 =DATE($A$2,$B$2,1+(($A8-(B$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(B$5-WEEKDAY(DATE($A$2,$B$2,1)))) C8 =DATE($A$2,$B$2,1+(($A8-(C$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(C$5-WEEKDAY(DATE($A$2,$B$2,1)))) D8 =DATE($A$2,$B$2,1+(($A8-(D$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(D$5-WEEKDAY(DATE($A$2,$B$2,1)))) E8 =DATE($A$2,$B$2,1+(($A8-(E$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(E$5-WEEKDAY(DATE($A$2,$B$2,1)))) F8 =DATE($A$2,$B$2,1+(($A8-(F$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(F$5-WEEKDAY(DATE($A$2,$B$2,1)))) G8 =DATE($A$2,$B$2,1+(($A8-(G$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(G$5-WEEKDAY(DATE($A$2,$B$2,1)))) H8 =DATE($A$2,$B$2,1+(($A8-(H$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(H$5-WEEKDAY(DATE($A$2,$B$2,1)))) B9 =DATE($A$2,$B$2,1+(($A9-(B$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(B$5-WEEKDAY(DATE($A$2,$B$2,1)))) C9 =DATE($A$2,$B$2,1+(($A9-(C$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(C$5-WEEKDAY(DATE($A$2,$B$2,1)))) D9 =DATE($A$2,$B$2,1+(($A9-(D$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(D$5-WEEKDAY(DATE($A$2,$B$2,1)))) E9 =DATE($A$2,$B$2,1+(($A9-(E$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(E$5-WEEKDAY(DATE($A$2,$B$2,1)))) F9 =DATE($A$2,$B$2,1+(($A9-(F$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(F$5-WEEKDAY(DATE($A$2,$B$2,1)))) G9 =DATE($A$2,$B$2,1+(($A9-(G$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(G$5-WEEKDAY(DATE($A$2,$B$2,1)))) H9 =DATE($A$2,$B$2,1+(($A9-(H$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(H$5-WEEKDAY(DATE($A$2,$B$2,1)))) B10 =DATE($A$2,$B$2,1+(($A10-(B$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(B$5-WEEKDAY(DATE($A$2,$B$2,1)))) C10 =DATE($A$2,$B$2,1+(($A10-(C$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(C$5-WEEKDAY(DATE($A$2,$B$2,1)))) D10 =DATE($A$2,$B$2,1+(($A10-(D$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(D$5-WEEKDAY(DATE($A$2,$B$2,1)))) E10 =DATE($A$2,$B$2,1+(($A10-(E$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(E$5-WEEKDAY(DATE($A$2,$B$2,1)))) F10 =DATE($A$2,$B$2,1+(($A10-(F$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(F$5-WEEKDAY(DATE($A$2,$B$2,1)))) G10 =DATE($A$2,$B$2,1+(($A10-(G$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(G$5-WEEKDAY(DATE($A$2,$B$2,1)))) H10 =DATE($A$2,$B$2,1+(($A10-(H$5>=WEEKDAY(DATE($A$2,$B$2,1))))*7)+(H$5-WEEKDAY(DATE($A$2,$B$2,1))))

Excel tables to the web >> Excel Jeanie HTML 4

As you can see B6, C6 and D6 should be blank.
The 2nd week (B7) should start with 10/5/08.
I've tried a few different If formulas in the second week, but can't seem to get it too work.

Any ideas would be great
Thanks
Matt

Hello,

I would like help on calculating business date after deducting a number from a date.

For example, if I have the date as 04/02/2008 in cell A1 and number 38 in cell A2, I want to calculate the difference between A1 and A2 and write that difference in cell A3 as the date (formatting the cell as date). However, I want to make sure that this value returned is a weekday. If it is not, then it should automatically go to the previous week day.

In the example mentioned above, the value falls on Sunday and I would like this value to automatically roll over to previous Friday.

Week Day = All days excluding Saturday and Sunday. Also, if Holiday can be excluded, it will be great.

Please suggest me the formula I can apply. Thanks a bunch for the help. I appreciate it in advance.

I have a date value in col1 (always a mon thru fri date) and in an adjacent column I want to put the value of the date that is 8 business days prior.

I can't subtract 10 days (10 to account for the weekend) using the function:
B2 = DATE(YEAR(A2), MONTH(A2), DAY(A2)-10) because Tuesday and Wednesday dates would land me on a Saturday or Sunday.

How do I get the date that is 8 business days from the week day value I have?

Hi,

I have a worksheet with date amounts in each column for each day of the year, the rows are location numbers. How can I pull up to another spreadsheet todays date amount and week to date amount, month to date amount, quarter to date amount and ytd amount without having to go into the cell and change the formula manually each time. My company is under a fiscal calendar 4,4,5 week periods and the week period runs from Sunday to Saturday.

Any help will be appreciated!

Thanks,

Hi, please someone help me!
I have a colums with dates, all are consecutive days. I formatted it to
display the days of the week and date ( Tuesday, April 19, 2005) I have
another column where I want to calculate weekly average from the first day of
the week, sunday, to the last saturday. So in this column I want to write a
conditional formula that if the date column in the same row is saturday then
calculate the average for seven days above including saturday if not the
display a blank. I'm trying to use =if( ) function but I don't know hor to
tell the program to recognize if the date is saturday. Please help me. Thank
you
Thalia

Hi,

I have read numerous postings on how to calculate week numbers in a year, but none seems to work correctly for what I need.

I need to calculate week numbers from years 2000 to 2030 so a lot of leap years are encompassed. The week runs Sunday to Saturday and Week 1 in any given year should start on the first Sunday of that year.

The closest formula I have found to what I need is:
=(A1-WEEKDAY(A1)-DATE(YEAR(A1+7-WEEKDAY(A1)),1,1))/7+1
However, here are issues with the results:
01-01-2000 (Sat) - Week 0 (correct)
02-01-2000 (Sun) - Week 1 (correct)
31-12-2000 (Sun) - Week 1 (incorrect, should be Week 0)
This should not be Week 1 as this date is still in 2000. This is a leap year and I am wondering whether this is part of the problem.
07-01-2001 (Sun) - Week 2 (incorrect, should be Week 1)
This should be Week 1 as this is the first Sunday of the year 2001.

I don't mind if the Week 0 dates become Week 53/54, but I do need Week 1 to start on the first Sunday of each year.

Any help would be greatly appreciated!!

Thanks,
Jude

Hi, I have searched and did not find an answer I could use. What I am trying to do is count the number of hits within 7 days and 30 days. When I receive the data, it is not in order by date and would like to keep it that way.

Here is a sample of the data:

What I want to do is say count from today - 7 days and from today - 30 days.
Code:

metricIndex		CreateDt
39175	2	Sunday, May 10, 2009
36233	20	Sunday, May 10, 2009
36233	20	Sunday, May 10, 2009
35938	15	Sunday, May 10, 2009
41346	15	Saturday, May 09, 2009
36259	15	Saturday, May 09, 2009
36255	15	Saturday, May 09, 2009
36257	15	Saturday, May 09, 2009
38654	15	Saturday, May 09, 2009
34885	2	Saturday, May 09, 2009
36264	20	Saturday, May 09, 2009

Any help on the mater would be greatly appreciated.

Sorry if this is a total Noob question.

-Joe

I was wondering if there was a way to Count Number of Days between Two Dates (Excluding Sundays and Holidays Only - including Saturdays). I have a simple spreadsheet where I have a Date Received Column(H3) and a Date Completed (J3) column and all of the functions I have tried to use exclude both weekend days and not just Sunday. For example,

H3 J3

11/16/2011 11/29/2011

The result should be 11 but because the 19th and 20th is a Saturday/Sunday I am getting an incorrect value (should be excluding 11/20/2011(Sunday), 11/27/2011(Sunday) and 11/24/2011(holiday)).

Any ideas??

Thats it really - i'm looking for a macro that generates the week ending date for last week - of the date for last sunday.

I have looked at using
Code:

Sub trying()
Dim dotw As String
Dim today As String
today = Format(Date, "dddd")
If today = "Monday" Then dotw = 1
If today = "Tuesday" Then dotw = 2
If today = "Wednesday" Then dotw = 3
If today = "Thursday" Then dotw = 4
If today = "Friday" Then dotw = 5
If today = "Saturday" Then dotw = 6
If today = "Sunday" Then dotw = 7

MsgBox (Date - dotw)
End Sub

which works - but is there a simpler way - i'm guessing this could be done with 1 line somehow.

I have a range "Date" which has all the dates in a quarter. I'd like code that looks at each date and compares it to 2 other ranges, also dates, "MajorHoliday" and "MinorHoliday". It also looks at the date to see if it is Saturday or Sunday, something like: Weekday(dte.Value, vbMonday) > 5. If the date meets any of these criteria it stores the location of the cell as the beginning of a range to which the next cell is added if it meets the criteria. As an example: If the date is Saturday it would be the first cell in the range and since the next date is Sunday then the range would be the two cells and if the next date is Labor day then it would be a three cell range. This range would be formatted in a certain way, but I have figured that out (with help from you). Another example would be if Christmas was on Friday then the range would start from that date and end at Sunday, another 3 cell range. Similarly Thanksgiving would always be a one cell range.

I hope I am clear.

Thanks for the help.

Hi All,

I'm trying to take circa 7 days from from a date but not either round up or down depending on if the resulting answer is a Sunday or a Saturday.

E.g. date Sunday 26/6 less 7 days returns Sunday 19/6. I would like to return an answer of Monday 20/6. Likewise if the same formula was applied to Saturday 25/6 then I would want answer of Friday 17/6.
I've tried playing around with the Workday function but I feel it's not that simple and may require an If statement somehow.

Many thanks in advance.

Moz

Hello:

Any help with this would be greatly appreciated. I need help with rounding a due to date to the earliest friday. For example:

If order is received on 6/24/10 (A4) then confirmation must be sent out within ten days

A4 + 10= 7/4/10. The 4th is a saturday.

However, if the result is a saturday or sunday, it needs to be rounded to the Friday before.

I also need to subtract in the same manner.

Ie: if due date is 11/1/10 then reminder letters must be sent out 15 days before.

A4-15 = 10/17/10 which is a sunday and needs to become Friday.

Please help. thank you so much.