Force Week End Date to Be a Friday

redhots4

Board Regular
Joined
Aug 30, 2004
Messages
107
I typically run reports from Saturday AM to Friday PM and need to always display the 'week ending' date as the Friday of the week. If the report is always run on a Friday, I can just use the "TO" date as the week ending date and feel confident that it will be a Friday.

Example: Report 9/9/06 to 9/15/06. Week End date = Fri 9/15/06.

If no data is found on Friday, however, my stupid report ends on Thursday. The data is still considered week ending 9/15.

Given any date, how do I find the date that was Friday of that week and display it as the week ending date?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi redhots4,

To get the next week ending for any date, use

=A1+6-WEEKDAY(A1)

where A1 is the cell containing a date.

Note: I'm not sure which week you want the weekend to be in. This formula assumes the week starts on Sunday, so if you enter a Saturday's date it will give you the Friday the day before. If you enter a Sunday date you will get the following Friday. You can change this using WEEKDAY's second argument (return_type) to base it on a different week start day.

Keep Excelling.

Damon
 

Forum statistics

Threads
1,136,652
Messages
5,677,008
Members
419,668
Latest member
DharmaK

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