Create Get Railway Week Number Function in Microsoft Excel

Whilst working for a Railway engineering company in the UK you will often be asked what week number is it?

You may be creating a spread sheet where you input the date and instead of manually looking up the week number from a railway calendar on the wall, you want a function that does it for by typing in something like:
=RailwayWeekNumber(02/11/2015)

One way of doing it without making your spread sheet a macro enabled workbook would be to create a table in another sheet or hidden columns and use the VLOOKUP function but this would mean manually creating the table and keeping it up to date.

You could attempt to create a complex formula. However you'll soon discover that there are a few variables that need to be considered, and the nested IF Statements start to build.

This is a guide on how to create a module in excel using VBA, that will allow you to get the railway week number just by typing in the date.
It doesn't teach you how to code in VB but I will explain the logic I've used step by step.
Once I figure out how to copy my code neatly into HTML, I'll place it at the bottom of the article.

To start with we need to find out what day is day 1 of week 1. Once we know this we can count forward or back to the date we want to know the week number for.
To do this I looked for a pattern, something that occurs in every week one in every year. After looking through a couple of years worth of calendars I discovered that the 03rd April appears in every week 1 of every Railway Year.
A railway week starts on a Saturday and finishes on a Friday, so here's a couple of examples: if the 3rd of April falls on a Sunday then Day 1 of Week 1 will be Saturday 02nd April, If the 3rd April falls on a Friday the Day 1 of Week 1 will be Saturday 28th March.

So knowing the above I created a new module in my excel VBA editor and wrote my first function as below.

 

Once you've created the above you can test it. So type "=SaturdayDate(2015)" into a blank cell on you worksheet and it should return the 28/03/2015.

You might be thinking, shouldn't you be typing in a railway year like "2014/2015" or "2015/2016". This is done later on so we don't have to do it here.

 

So now all we have to do is compare our date to the Day1 Week 1 date worked out above for us right?

Nearly.... Lets say our date is "02/11/2015", typing "2015" as an argument into our SaturdayDate function would give "28/03/2015". The number of days difference is 219. Divide this by 7, ignoring the remainder gives us 31. This means we are 31 weeks from week 1 so the 02/11/2015 is week 32.

The above works fine, but what about a date before week 1 lets now try "02/02/2016".

Typing "2016" as the argument into our SaturdayDate function gives the "02/04/2016" as Day1 Week 1 in 2016.

All we need to do now is get the days difference between the two dates (60 days), divide this by 7 to get the number of weeks and take it away from 52(The amount of weeks in a year).

Unfortunately the above will not work, "2015/2016" is a 53 week railway year.

Because 365 divided by 7 is 52.143 weeks and 52.286 weeks in a leap year the railway has to throw in a 53 week year every so often to remove the accumulation of 0.143s and 0.286s. Usually every 5 to 6 years depending on leap years.

 

Before we write the main function to get the railway year we need to write a function to test if a railway year is 53 weeks or the standard 52.

 

 

Typing "=isFiftyThree("2015/2016")" into a cell in excel will give you the result of "True". A Boolean result which tells us "2015/2016" is a 53 week year. If you type in the next 5 years worth of railway years you'll discover the next 53 week year is "2020/2021".

Again to build this function I looked for patterns in previous calendars and discovered that if the last day of the railway year was Friday the 02/04/Year or Friday 01/04/Year on a leap year then it was a 53 week year.

After writing this article I discovered that if the difference between the 2 years Day1 Week1 dates was exactly 53 Weeks or 371 days then it was obviously also a 53 week year. This could have made for a more simple Function. But the above works to so I'll leave it as it is.

 

In the above function you may have noticed another function called "IsLeapYear". Unfortunately this isn't a built in function and we have to write it also.

 

 

Entering 29th February on a non leap year will return the same serial as the 01 March and hence the Month function will return 3. So it will only return 2 on a leap Year.

 

Now we have everything in place to make the "GetWeekNo( )" function. if the date is lower than the Day1 Week1 date we can test whether to take it away from 53 or 52.