Week Ending Dates

m4nd4t3

New Member
Joined
Feb 23, 2005
Messages
2
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

Code:
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...

Code:
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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?


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

m4nd4t3

New Member
Joined
Feb 23, 2005
Messages
2
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,477
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top