Calculate Day of Birth - Calculate Day of the Week for Any Date - Calculate Day of the Week in Excel

Jesus is Christ. In English, years before the birth of Christ are traditionally identified using the abbreviation BC meaning ‘Before Christ’. On the other hand, in Latin, years after the birth of Christ are traditionally identified using the abbreviation AD meaning ‘Anno Domini’. AD is another way of saying in the ‘Year of the Lord Jesus Christ’.

BC and AD are the dating system many people are using today to describe any date. But right when we are here, does this mean Jesus Christ was born on year Zero? How can Christ be born on year Zero? Give him something. Jesus was born in such a way that his year of birth defies all mathematical logic. Jesus was born somewhere between 1 BC and 1 AD but not at year Zero.

The year Zero is a popular imagination of the modern day person because there is nothing like year zero. The truth is that 1 AD follows immediately after 1 BC. The number of years between 1 BC and 1 AD is 1 year and not 2 years. This means that a guy who was born in 20 BC and kicked the bucket in 20 AD, would have died at age 39 years and not age 40 years.

Many countries are today using the Gregorian calendar. This calendar was proposed by a guy called Aloysius Lilius from Naples, Italy. A Pope by the name Gregory XIII decreed the calendar in a papal bull on 24th February 1582. Great Britain and its colonies took about 170 years to implement the changes which they did on 14^th September 1752.

In USA, areas controlled by Great Britain such as Washington, Oregon, Eastern seaboard etc, implemented the changes in 1752. Mississippi valley which was controlled by France implemented the changes in 1582 whilst areas under Spain such as Texas, Florida, California, Nevada, Arizona, New Mexico, etc, implemented the changes in 1582

Which day of the week were you born? Many people are very much aware of the date they were born but unfortunately very few know the day of the week when they were born. This is also true of very many other historical events – perhaps you know the date your grandfather died but you do not know the day of the week, or perhaps you know the date your father wedded your mother but do not know the day of the week when it happened. If you are like me, you would want to know what day of the week those historical events happened as well as future events such as your birthday 3 years from now.

You can easily calculate day of the week for any date. You can calculate day of the week using excel in two very simple steps as follows:

1. In a blank excel spreadsheet; enter the date of the event in cell A1. Example - 02/25/73. If you PC date setting is ddmmyy, then enter -25/02/73

2. On any other cell, say B1, Enter, ‘=TEXT(WEEKDAY(A2), "ddd")’. This is where the day of the event will appear.

That’s it. You now have the day the event happened or will happen. But there are two things to note:

1. If you are using an earlier version of Excel like office 2000, you may need to replace ‘ddd’ with ‘dddd’.

2. This will only work for dates after the year 1900.

But we need an excel spreadsheet that will work for dates before and after 1900. To do that, we need to define some parameters before putting them on excel. Let’s define the following, but bear in mind that how we arrive at the formula is beyond the scope of this article:

1. a = (14-Month)/12 – we take the integer only

2. y = Year - a

3. m = Month + (12 X a) – 2

4. day of the week =(day + y +y/4- y/100 +y/400 +(31 X m)/12) mod 7

5. The answer in 4 is 0 for Sunday, 1 for Monday, 2 for Tuesday, etc.

All the calculations must be truncated so that we take only the integers. Mod 7 means we divide by 7 but take only the remainder. For example 9 mod 7 means 9/7 which is equals to 1 remainder 2 and we therefore take 2 as the answer.

To cut a long mathematical algorithm short, we shall go straight and input this formula in excel spreadsheet following the steps below:

1.     In a blank excel spreadsheet; enter the name ‘Day’ in cell A1, ‘Month’ in cell B1, ‘Year’ in cell C1

2.     In cell A2 enter the day, say ‘14’, in cell B2 enter month, say,’9’ and in cell C2 enter month, say, ‘1752’.

3.     In cell D2 enter the formula, “=TRUNC((14-B2)/12)”

4.     In cell E2 enter the formula, “=C2-D2”

5.     In cell F2 enter the formula, “=B2+(12*D2)-2”

6.     In cell G2 enter the formula, “=MOD((TRUNC(A2)+TRUNC(E2)+TRUNC(E2/4)-TRUNC(E2/100)+TRUNC(E2/400)+TRUNC(31*F2/12)),7)”

7.     In cell H2 enter the formula, “=IF(G2=0,"Sun",IF(G2=1,"Mon", IF(G2=2,"Tue",IF(G2=3,"Wed",IF(G2=4,"Thur",IF(G2=5,"Fri",IF(G2=6,"Sat","Error")))))))”

8.     The ‘day of the week for any date’ is read in cell H2.

You should get the answer that the date 14^th September, 1752 was on Thursday, which is the day the Great Britain and its colonies implemented the use of the Gregorian calendar.

At around the year 525 AD, there was this fellow called Dionysius Exiguus who was a monk from Scythia. Dionysius was also known as Denis the Little One. Dionysius was also a canon in the Roman Curia. At that time, the Pope had given instructions that Dionysius should prepare calculations for the dates of Easter within a time frame the pope had given. On his part, Dionysius Exiguus deliberately skewed the dates such that the counting would start in favor of Jesus Christ rather than the norm which was to count years since the reign of Emperor Diocletian. The hypothesis is that Dionysius the little one may have through the fear of Pope or Christ fixed Jesus Christ’s birth wrongly.

^{If you have liked this article, and you would want this page to keep up and improved, you can help by purchasing some great items from Amazon by following Amazon links and widgets on this page. A free way to help would be to link back to this webpage from your web page, blog, or discussion forums.}

^{The Author’s page is designed to help beginners and average readers make some money as an extra income to supplement what they may be earning elsewhere - details of which you can find in My Page, if you will.}