Excel weekending date formula

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.
LVL 19
Edward PamiasTeam Lead RRS DeskAsked:
Who is Participating?
 
Bill PrewCommented:
This should work (lots of ways to get there...).

=N2+7-WEEKDAY(N2+1)


»bp
1
 
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Thanks Bill! I have another question coming. I hope you can help.
0
 
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Thank you!
0
 
Rob HensonFinance AnalystCommented:
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
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.