Force Week End Date to Be a Friday

redhots4

Board Regular
Joined
Aug 30, 2004
Messages
120
Office Version
  1. 365
Platform
  1. MacOS
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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,171,062
Messages
5,873,555
Members
432,983
Latest member
sam97

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