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

BC and AD

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’.

Could Jesus Christ Have Been Born On Year Zero

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.

Popular Imagination of the Modern Day Man

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.

Gregorian calendar

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 14th September 1752.

Gregorian calendar in USA

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?

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.

Calculate Day of the Week in Excel

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.

Calculate Day of the Week for Any Date before and after 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.

Calculate Day of the Week for Any Date Using Excel

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 14th September, 1752 was on Thursday, which is the day the Great Britain and its colonies implemented the use of the Gregorian calendar.

AD, Anno Domini, is a dating system that was devised in 525 by Dionysius Exiguus. Dionysius Exiguus used Anno Domini to compute the date of the Christian Easter festival. Image Credit: Wikipedia
AD, Anno Domini, is a dating system that was devised in 525 by Dionysius Exiguus. Dionysius Exiguus used Anno Domini to compute the date of the Christian Easter festival. Image Credit: Wikipedia

Calculations for the Dates of Easter

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.

More by this Author


Comments 3 comments

Hello, hello, profile image

Hello, hello, 6 years ago from London, UK

Very ineresting information and a well weritten hub. Thank you.


munirahmadmughal profile image

munirahmadmughal 6 years ago from Lahore, Pakistan.

"Calculate Day of Birth-CAlculate Day of the Week for Any Date-Calculate Day of the Week In." This is a good hub. Anything that promotes awareness in mankind and helps in performance its functions and creating confidence in it concerning its truth is a good thing.The hub has added to knowledge and also made application easy and understandable. All things including mankind, on the earth are indicators towards their Creator. His creation is accurate and precise and speaks of the attributes of the Creator being unparallelled. Nothing is futile. Nothing is without purpose. Good and evil have been made distinct and clear. Law of reward and punishment is there. Heart, mind, limbs, vision, hearing, smelling, touching and tasting are there to use and utilize. Birthday tells us that one year has gone out of the appointed end time. What we gained and what we lost is to be assessed so that one may leave this world with something in balance. Good deeds and good children left behind are coninuing virtues and asset. Blessing be on all.


EDWARD FEEHAN 3 years ago

OF COURSE FOR DATES THAT ARE NOT COMPLICATED ONE CAN EASILY SEE WHAT DAY OF THE WEEK THEY WERE ON BY FORMATTING THE CELL OR COLUMN TO SHOW THE DATE AND DAY OF THE WEEK IN EXCEL. I DON'T KNOW HOW FAR BACK EXCEL GOES VERSION WISE TO BE ABLE TO DO THIS BUT RECENT VERSIONS HAVE THAT CAPABILITY.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working