Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Insert Row By Date

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

My worksheet lists in column (A) Account Name, (B) ID #, (C) Date Received, (D) Date needed, and (E) Date Completed.

I need a marco button for my worksheet to add one row for each week, in which, will list Saturday and/or Sunday under the Account Name under Column (A) & the exact date of that Saturday and/or Sunday under the Date Received, Column (C), for each week (Mon-Sunday) throughout the report. The rest of the row is blank.

If there are multiple accounts on the same dates that Sat or Sunday falls on. The row can be inserted under the last duplicate account name with the same date received.

So I need only one Saturday Row and Only one Sunday Row for each week insterted in my worksheet.

Any Help is appreciated


Similar Excel Video Tutorials

Helpful Excel Macros

Return the ISO Week Number from a Date in Excel - UDF
- Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the
Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
Format Cells in The Long Date Number Format in Excel
- This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
WEEKNUM UDF for Excel Versions That Don't Contain This Function - UDF
- This is a WEEKNUM UDF for Excel. This allows you to determine the number of the week in the year on which a particular

Similar Topics







I have a sheet that shows our on-call rotation.

B C 2 Schedule Starting Date: 3 Week Start Week End 4 Sunday, October 31, 2010 Saturday, November 06, 2010 5 Sunday, November 07, 2010 Saturday, November 13, 2010 6 Sunday, November 14, 2010 Saturday, November 20, 2010 7 Sunday, November 21, 2010 Saturday, November 27, 2010 8 Sunday, November 28, 2010 Saturday, December 04, 2010 9 Sunday, December 05, 2010 Saturday, December 11, 2010 10 Sunday, December 12, 2010 Saturday, December 18, 2010 11 Sunday, December 19, 2010 Saturday, December 25, 2010 12 Sunday, December 26, 2010 Saturday, January 01, 2011

What I'd like to do is have people be able to enter the date into C2 and have the sheet automatically eliminate any dates that are no longer valid. So, for example if they put 11/15/10 into C2, it would eliminate row 4 and 5 so that row 6 is at the top.

Any suggestions?


Hi Guys!

This is my very first request for help. Please be gentle!

Okay, what I am looking for is a formula which looks at a date (DD.MM.YY) and if the date falls on a Saturday or Sunday it returns the date for the previous Friday.

In other words, if the date being examined was 14.11.09 or 15.11.09 (Saturday and Sunday respectively) then the date displayed would be 13.11.09.


Hello out there in 'Excel' Land,

I have a 'small' issue, with which I hope someone can help me.

In the following table, I have the task of finding the last '???day' in a particular month.

What I know, or can calculate, is the first day of said month (which could start on any day of the week (bottom row!), and also have 28, 29, 30 or 31 days.

My task is to:
LookUp '???day' in column 'N' -equating to the first day of that month - where N is between 1 & 7 and:
Return the Day-In-Month value in Col:1 (or 9 in this case) in the Row where I found the day being sought.

Table:
1 2 3 4 5 6 7 31 Tuesday Wednesday Thursday Friday Saturday Sunday Monday 31 30 Monday Tuesday Wednesday Thursday Friday Saturday Sunday 30 29 Sunday Monday Tuesday Wednesday Thursday Friday Saturday 29 28 Saturday Sunday Monday Tuesday Wednesday Thursday Friday 28 27 Friday Saturday Sunday Monday Tuesday Wednesday Thursday 27 26 Thursday Friday Saturday Sunday Monday Tuesday Wednesday 26 25 Wednesday Thursday Friday Saturday Sunday Monday Tuesday 25 24 Tuesday Wednesday Thursday Friday Saturday Sunday Monday 24 23 Monday Tuesday Wednesday Thursday Friday Saturday Sunday 23 22 Sunday Monday Tuesday Wednesday Thursday Friday Saturday 22 21 Saturday Sunday Monday Tuesday Wednesday Thursday Friday 21 20 Friday Saturday Sunday Monday Tuesday Wednesday Thursday 20 19 Thursday Friday Saturday Sunday Monday Tuesday Wednesday 19 18 Wednesday Thursday Friday Saturday Sunday Monday Tuesday 18 17 Tuesday Wednesday Thursday Friday Saturday Sunday Monday 17 16 Monday Tuesday Wednesday Thursday Friday Saturday Sunday 16 15 Sunday Monday Tuesday Wednesday Thursday Friday Saturday 15 14 Saturday Sunday Monday Tuesday Wednesday Thursday Friday 14 13 Friday Saturday Sunday Monday Tuesday Wednesday Thursday 13 12 Thursday Friday Saturday Sunday Monday Tuesday Wednesday 12 11 Wednesday Thursday Friday Saturday Sunday Monday Tuesday 11 10 Tuesday Wednesday Thursday Friday Saturday Sunday Monday 10 9 Monday Tuesday Wednesday Thursday Friday Saturday Sunday 9 8 Sunday Monday Tuesday Wednesday Thursday Friday Saturday 8 7 Saturday Sunday Monday Tuesday Wednesday Thursday Friday 7 6 Friday Saturday Sunday Monday Tuesday Wednesday Thursday 6 5 Thursday Friday Saturday Sunday Monday Tuesday Wednesday 5 4 Wednesday Thursday Friday Saturday Sunday Monday Tuesday 4 3 Tuesday Wednesday Thursday Friday Saturday Sunday Monday 3 2 Monday Tuesday Wednesday Thursday Friday Saturday Sunday 2 1 Sunday Monday Tuesday Wednesday Thursday Friday Saturday 1


If anyone has suggestions - other than looking in the calendar for that year - or alternative methods, I would be more than thankful!

Just as matter of interest: could I also 'Find' the second or further instances of my - and if so; how?

Thank you all for your 'N00bie' patience,


Brgds

David D.


Hello,

I have developed a spreadsheet to help estimate an expected delivery date for projects undertaken by our R&D team (using the Critical Path Method--Latest finish date). I have used the WEEKDAY formula to identify a delivery date during the workweek...so, I have programmed the formula to add 1 day to the expected delivery date if the initial delivery date falls on a Sunday and add 2 days if the initial expected delivery date falls on a Saturday.

The table I constructed to help identify an expected delivery date is inserted below, along with the formula I used.

=IF(VLOOKUP(CD48,Days1,5,FALSE)="Saturday",CD47+2,IF(VLOOKUP(CD48,Days1,5,FALSE)="Sunday",CD47+1,CD47))

Days Of Week 1 Sunday 2 Monday 3 Tuesday 4 Wednesday 5 Thursday 6 Friday 7 Saturday Projected Completion Project Completion--Projected 05/31/08--adds late finish date to expected start date Day of Week--Projected 7--looks up what day of the week the above date falls on Revised Completion Date (no weekends) Projected Completion--Actual 06/02/08--if the above day of week is a saturday or sunday, the formula above calculates the next work day Day 2--identifies new day of week Day of Week--Actual Monday

My question is whether I can revise the formula or use a different formula to also consider company holidays. I was told that such a formula does exist and would require me to create a list of non-working days to reference.

Can someone please offer me some direction? Thanks in advance for your help.


Hi,

My users have a task that takes 5 days to complete. I need to identify whether the completion date falls on a saturday or a sunday. If it falls on a sunday, the task needs to be moved back 2 days, and 1 day for a saturday.
Does anyone know how to do this? Thanks in advance,

Michael


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.




I have a patient visitation spreadsheet. Normally, when a patient is seen a 30 day followup will be posted.
I want to post a visit date and the next cell will display the scheduled 30 day date. No check for Saturday, Sunday or Holidays.
I have all holidays listed in another worksheet.
My current formula is: =IF(NOT(ISERROR(DATEVALUE(TEXT(D4,"mm/dd/yyyy")))),D4+30,"")
What I what is to select the very next date after Saturday, Sunday or Holiday.
Any assistance work be greatly appreciated.
Thank you,
Glenn



Good Afternoon,

I need help coming up with a formula that will return FISCAL WEEK based on the US Government Fiscal Year. US Government Fiscal Years run from Oct 1 - Sep 30. Moreover, the weeks run from Sunday (start) to Saturday (end). I have found a variety of formulas (such as the below) on various forums but none of them work for me. The main problem is that I cant get the formulas to see Sunday-Saturday as a week. Instead all the formulas I try run from Monday-Sunday. Please help. I am desperate! I know there is probably a simple answer but I am missing it.

Thank you,
Christina

=INT((A1-WEEKDAY(A1+1)-DATE(YEAR(A1)-(MONTH(A1)<10),10,1))/7)+2

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).




Hello all,

I need an excel formula that will add a given number of days to a starting date, but if the result date is a Saturday or Sunday it is moved to Monday.

I know how to add days to a given date but I was thinking of using an if statement for if it lands on a Saturday or Sunday but am having trouble writing the formula.

Perhaps an IF statement isn't the best way?

Thank you!
Rania

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.




Hi there,

I have a list of dates and I need a formula that will return the date of the following Monday, IF the date falls on a Friday, Saturday or Sunday.

Can anyone help?

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,

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, 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 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??