Week Ending Dates

[m4nd4t3]

I know there are alot of posts already regarding Days of the week and the week of the year "weeknum()", but I have not figured out how to best apply them. What I am trying to do is to have a series of rows with people and there start dates and end dates. Then I will take the Min() and Max() dates and have my columns autofill in the the weekending dates

Name         Start            End        2-25-05  3-04-05   3-11-05
===================================================================
John        2-25-05         2-28-05      XXXXXX    XXXXXX   
Joe         2-25-05         2-25-05      XXXXXX
Bob         3-01-05         3-11-05                XXXXXX   XXXXXX

I have figured a way to make it work but I know there has to be a better way...

Pertinent cells:  D3 = Min Start Date
                  E3 = Max End Date
                  F4 = First column that assumes the beginning date
                  G4 = Builds on preceeding column and so forth...

Forgive the extra garbage. Trying to stop error messages and still make it functional.

My first column (where the 2-25-05 is) has the following function:
=IF(WEEKDAY(D3)=6, D3, IF(WEEKDAY(D3)=7, D3+6, D3+(6-WEEKDAY(D3))))

This makes sure that we capture the first Friday regardless... all of the following coilumns build off of the preceeding column... until we are greater than the end date:
=IF(ISERROR(IF($E$3<(F4+7), "", F4 + 7)), "", IF($E$3<(F4+7), "", F4 + 7))

I tried to keep this as simple as I could... Would be more than happy to clarify this if you have any questions. Or perhaps I could post the worksheet.

http://www.planetroberts.com/files/weekendingdates.xls

 

[fairwinds]

Hi,

I'm not sure I understand your question.

=(INT((E2+7)/7)*7)-1
should give the dete of the next friday. Does that help?



The spreadsheet you linked to seems to hold actual, sensitive information. If so I suggest you delete it a.s.a.p.

 

[m4nd4t3]

Oops, I missed the other worksheets. All corrected. I appeciate the help!

Your formula works but I don't exactly understand how its working. You are using the raw number instead of the date. Can you explain how you are manipulating it?

 

[fairwinds]

My formula works on the basis that dates in excel is stored as a serial number starting with 1-1-1900 (which is a Sunday)
Thus any serial date that is a multiple of 7 is a Saturday.

Dividing a date with 7 and take only the integer part gives a Saturday. deducting 1 gives a Friday.