Excel weekending date formula

Edward Pamias
Edward Pamias used Ask the Experts™
on
All, I know I have asked this before but I cannot find it in my answered questions. :( Can someone give me a weekending date based on the date of the dates in the "N" column starting at N2. Any assistance is appreciated. I need it to show the Fridays date please.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Test your restores, not your backups...
Top Expert 2016
Commented:
This should work (lots of ways to get there...).

=N2+7-WEEKDAY(N2+1)


»bp
Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Author

Commented:
Thanks Bill! I have another question coming. I hope you can help.
Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Author

Commented:
Thank you!
Rob HensonFinance Analyst

Commented:
You can also make use of the CEILING function, syntax is:

=CEILING(Number, Factor)

So for your scenario:

=CEILING(N2,7)-1

This works because of the way that Excel uses dates, the first date in Excel's calendar is "1 Jan 1900" which was a Sunday and is represented as serial number 1; each Saturday thereafter is a factor of 7. The CEILING function will round up a number to the specified factor, so rounding up a date to a factor of 7 will be the following Saturday; less 1 is therefore the Friday.

The opposite function for rounding down is FLOOR and will round down to the factor.

Thanks
Rob

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial