Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# 52/53 week year end date formula

Posted on 2016-10-12
Medium Priority
335 Views
52/53 week year end date calculator

We want to know if there is an Excel formula that serves as a 52/53 week year end date calculator for all situations. Such a formula would answer a question of this nature:
•      For the next 20 years, what is the mm/dd/yyyy for a company whose year end date is the last Saturday in December or
•      For the next 20 years, what is the mm/dd/yyyy for a company whose year end date is the closest Sunday in January

The required input values would include:
•      Month
•      Year
•      Year end day of week, e.g., Saturday
•      Closest day to calendar month end or last day in month

We work with excel enough to be handy with using VBA to automate tasks like this, so we are open to a solution that would involve us plugging code into a VBA module.

Will award full points for a useful solution.

Thanks
0
Question by:Lee Richardson
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3

LVL 27

Expert Comment

ID: 41841160
Given the year is in cell A2,

Last Saturday of that year:
=(DATE(A2,12,31)-IF(WEEKDAY(DATE(A2,12,31))=7,0,WEEKDAY(DATE(A2,12,31))))

First Sunday of the following year:
=(DATE(A2+1,1,1)+1)+IF(WEEKDAY(DATE(A2+1,1,1)+1)=1,0,8-WEEKDAY(DATE(A2+1,1,1)+1))

This is based on a Sunday-Saturday week.  My gut tells me this could be simplified, but this works.
EE-28976075.xlsx
0

LVL 33

Expert Comment

ID: 41841503
You can probably use a combination of EOMONTH and CEILING/FLOOR functions.

Syntax for EOMONTH:

=EOMONTH(Date,Offset)  returns the last date of a month based on number of months offset from date.  0 gives the end of the same month, 1 next month, 2 gives next again etc. You can also use -1 for previous month.

Syntax for CEILING or FLOOR:

=CEILING(Number,Factor)  this will round the number to the given factor.  As dates are essentially stored as numbers within excel and the fact that the historical day 1 in the system was a sunday, using CEILING/FLOOR on a date with a factor of 7 will round a date to the preceding or following Saturday (FLOOR rounds down, CEILING rounds up). This works in a similar way to MROUND but with the direction of rounding specified.

If you upload a file with some sample inputs and required results, we can work on specifics.

Thanks
Rob H
1

LVL 33

Expert Comment

ID: 41842106
Last Saturday of Year:

=FLOOR(DATE(A2,B2,C2),7)   A2 = Year number, B2 = Month number, C2 = Day number.

Closest Saturday:

=MROUND(DATE(A2,B2,C2),7)   same references

First Sunday of following year:

=CEILING(DATE(A2,B2,C2),7)+1  same references

If it will always be December, then the Month and Day can be changed to fixed values (12 & 31). This could be useful as DATE function can be too clever for its own good. For example, knowing that November has only 30 days, look at this:

=DATE(2016,11,31)

You would expect it to give an error as 31 November doesn't exist; however the DATE function will return 1 December. It works out the date progressively. Year 2016 starts on 1 Jan, 11 months later is 1 November, (strictly speaking I know its only 10), 31 days later is 1 December. Likewise, if for some reason Month was set to 13 it would return a date for a year plus one month.
0

LVL 33

Accepted Solution

Rob Henson earned 2000 total points
ID: 41842273
Take a look at the attached.

Sheet1
Gives a list for the next 25 years. Change values in row 2 for Year, Month and Day to give effective "Year End Date". Month dates can only be 1 to 12 and Day number will be restricted based on month number.
Then change entry in E2 to give last day of period, drop-down with Sunday through Saturday to choose from. Columns H to L then give the calculated year end date based on 3 options:
1) Last date corresponding to chosen day
2) First date after the Effective Year End corresponding to chosen day
3) Closest date to the Effective Year End corresponding to chosen day

Sheet2
Calculations with options as the above for one specific effective year end date.
Enter year in A3, choose month in B3, choose Day in C3
Choose Last Day in E3 and basis of calculation in G3.
Calculation shows in A7.

I have done some testing for different scenarios and those that I tested came back OK but you might want to do some more extensive testing before taking the results as final.

Thanks
Rob H
Year-End-Dates.xlsx
1

Author Closing Comment

ID: 41847396
Looks great! Thanks, Rob.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
###### Suggested Courses
Course of the Month5 days, 13 hours left to enroll