You can work with dates in Excel.
This means you can calculate with them and that is insanely handy. This way, you can see what day it is today, on what day of the week Christmas is and how many days it’s still away. Let’s look at some examples:
The formula with the function =TODAY() is placed in cell E1. This formula gives the current (system) date.
Cell E2 contains the regular typed date, 5-12-2017, which Excel automatically recognizes as a date.
I typed the formula: =E2-E1 in cell E3. This is where the two dates are subtracted. The result is a value representing the amount of days between the two dates.
Excel assigns a number to each date internally. That happens, of course, subsequent. This makes it possible to calculate with dates.
Cell F2 contains the function =WEEKDAY(E2). This function gives a number (1-7) representing a day of the week, wherein Sunday=1 and Saturday=6.
Knowing this, you can make a table and find the day of the week in words with the function VLOOKUP, as showed in G2.
Some more examples of other date-functions Excel contains:
Cell E1 contains the date of the week with the function TODAY().
Cell E2 takes the day out of the date using the function DAY(E1).
Cell E3 takes the month out of the date using the function MONTH(E1).
Cell E4 takes the year out of the date using the function YEAR(E1).
The cells F2:F4 is one higher than the results from E2:E4.
I create a new date in cell F5 using the function DATE(F4;F3;F2), so using the year first, then the month, then the day.
In E5 I will determine what part of the year it is today using the function YEARFRAC(”1-1-2017”;E1;1). The last 1 is needed to remind Excel to calculate with actual days in the year. For 2017 that means 365. Cell H1 contains the number belonging to TODAY and cell H2 the one belonging to 1-1-2017. Cell H3 contains the difference between the two dates in days. The amount of days in 2017 are placed in cell H4. The formula: =H3/H4 is stored in cell H5, giving the exact same value as the one in cell E5. This shows you how Excel calculates internally.
Finally, cell E6 shows the date in 7 weekdays. To do this, use the function WORKDAY(E1;7). This function takes the weekends in account and doesn’t count them. As third argument, you can enter another set of dates, like holidays, which can also be kept out.
How many days in a year?
In our calendar, that depends on it being a leap year or not. A leap year has 366 days and the others have 365. So we need to know what years are leap years. Normally, that’s every 4 years, when the year is divisible by 4. So 2016 is leap, because the number 2016 is divisible by 4. 2017 is not leap, because 2017 isn’t divisible by 4, it leaves 1.
There are some exceptions, however. Century changes aren’t leap. So 1800 and 1900 weren’t leap. But even that has its own exceptions, because once in 400 years, a century change IS a leap year. The year 2000 was a leap year, but a special one…
All the rules together give the next formula to calculate the amount of days in any giving year:
=365+IF(AND(MOD(F1;4)=0;OR(MOD(F1;100)<>0;MOD(F1;400)=0));1;0), in which the year is placed in cell F1.
Conclusion
Because Excel is smart enough to convert a date into a number, you can calculate with them and there are a lot of handy functions you can use to do so.
Problems
I am in the happy condition of having my two parents still alive, very old though, but still… I wondered when my mother will be surpassing my grandmother in age. To figure this out, I made the following sheet:
Cell E1 contains the birth date of my grandmother. Cell E2 has the date of death of my grandmother, and cell F1 contains my mother’s date of birth.
I want the difference in days between the date of birth and the date of death of my grandmother in cell F2, so I can add this number to my mother’s date of birth. This way I can see when my mother will be older than my grandmother.
But, to my surprise, an error occurred. Research shows that Excel can only work with dates from the 20th century.
It gets worse: According to Excel, there’s a 29-2-1900, while we saw that 1900 was NOT a leap year. Microsoft explains that they did this because of compatibility reasons relative to Lotus 1-2-3 (see the article Microsoft wrote about this). This has consequences for the function WEEKDAY, which gives a wrong outcome for dates from 1-1-1900 to 29-2-1900.
Also for genealogists, to whom I have lately counted myself, this is an issue because this group of people often comes across dates before 1900.
HJGSoft has found a solution to this problem and has implemented it.
The add-in Adequate (version 2017_v1) contains 12 functions to make it possible to work with ‘all’ dates.
The system works as follows:
Depending on the entered date, Adequate checks if the date is Gregorian or Julian.
All dates over 15-10-1582 are Gregorian. All dates up to 4-10-1582 are Julian. Pope Gregory made ‘his’ calendar start at 15-10-1582, one day after 4-10-1582. So there’s a 10 day hole in our calendar. This has to happen because the Julian calendar didn’t handle leap years well. The Julian calendar assumes that one solar year lasts 265,25 days, meaning there has to be one leap year every four years to make up for one year. But actually, a solar year only contains 365,2425 days, which needs an extra correction. Hence, the difficult leap year formula we saw before.
Adequate converts a date into a number as follows: 15-10-1582 gets a 0 and from there on 1 day is added. So all dates in the Gregorian calendar have a positive value. All dates calculated back from 4-10-1582 get 1 less. So 4-10-1582=-1, 3-10-1582=-2 etc. With that, all Julian dates get a negative value.
Furthermore, in our calendar era, we don’t have the year 0. So the day before 1-1-1 is 31-12–1 or 31-12-1 B.C. (Before Christ).
Now, we can also calculate with this system. This will happen with the specially designed functions. The date has to be entered as a text in the format dd-mm-yyyy.
A couple of important functions in this system:
HJG_UDate2Number converts a date to a number
HJG_Number2UDate converts a number to a date
HJG_UDays calculates the difference in days between two dates
HJG_UDateAdd adds the amount of years, quarters, months, weeks or days to a date
HJG_UWeekDay determines the day of the week, wherein Sunday=0 and Saturday=6
HJG_UDateFormat converts a date to a certain format
We are finally able to fix my problem with all of these tools:
The cells E1, E2, and F1 contain respectively my grandmother’s date of birth, my grandmother’s date of death and my mother’s date of birth.
Cell F2 contains the formula =HJG_UDays(E1;E2)+1. HJG_UDays determines the amount of days my grandmother has lived.
Cell F3 contains the formula =HJG_UDateFormat(HJG_UDateAdd(”d”F2;F1);”Dddd Mmmm d yyyy”).
The function HJG_UDateAdd(”d”F2;F1) adds the amount (F2) of days (”d”) to the date (F1).
Next, the date will be formatted using the function HJG_UDateFormat to the format Dddd Mmmm d yyyy, where Dddd is the day of the week fully spelled out, starting with a capital, followed by the month fully spelled out, also starting with a capital, and followed by the day without a leading zero, and finally the 4-numbered year.
My mother will have passed my grandmother in age at Tuesday the 6th of June 2017. Now let’s just hope she’ll make it…
Update: My mother made it!