ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

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

Updated on March 27, 2011

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.

Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      EDWARD FEEHAN 5 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.

    • munirahmadmughal profile image

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

    • Hello, hello, profile image

      Hello, hello, 7 years ago from London, UK

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

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: "https://hubpages.com/privacy-policy#gdpr"

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)