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.
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you!
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
=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
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER