Force Week End Date to Be a Friday

redhots4

Board Regular
Joined
Aug 30, 2004
Messages
136
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

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
Back
Top