TIME conversions in Microsoft Excel.
We’ve covered a lot of ground with posts on date calculations in Excel: using the DATEDIF function, calculations without the DATEDIF function , and in the most recent post – business day calculations with the NETWORKDAYS funtion. Now it’s turn to perform some TIME conversions and calculations in Excel. We will use TIME, TIMEVALUE, NOW, HOUR, MINUTE, SECOND, TEXT, and MOD functions to perform various time operations…
NOW function displays the current date and time. Depending on your cell formatting, it might be date only, or if your cell format is General, it could even be serial number equivalent of your current date and time. While this function has no arguments, its syntax still calls for a set of parenthesis: =NOW(). We already know that Excel uses the whole value of 1, in reference to the “beginning of time”, as far as Microsoft is concerned – January 1, 1900. Similarly, today’s date has the value of 42,124 The decimal point value references the fractional time portion of any date. As an example, 0.5 denotes NOON, while 0.75 refers to 6 PM. [0.75*24 = 18] Similarly, one minute, is 1/60th of an hour or 1/1440th of a day, calculating to be 0.069(4). Keep in mind that, whenever date/time value starts with a 0, the date portion has no value, and we are working with the time value only.
Performing business days calculations in Excel, with NETWORKDAYS function.
We already did some date calculations with a DATEDIF function, as well as without one . However, the limitation of both methods was the fact that they focused on calendar day calculations; in this post, we will perform business day calculations. To accomplish this task, we will need to use NETWORKDAYS, and possibly NETWORKDAYS.INTL functions.
Following our tradition, let’s turn to Microsoft’s own documentation to introduce the NETWORKDAYS function: “Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays.”
Working with sample datasets in BigQuery
In the previous post we added public tables to our BigQuery interface. However, Google already provides sample data on various topics by default. While most of these tables are not updated, they still present some interest in terms of learning trends or insights on a multitude of topics. We will focus on 3 of these tables:
Natality (daily US births from 1969 to 2008),
GSOD (daily weather information by a station number from 1929 to 2009),
and Shakespeare (word index of Shakespeare’s works.)
Let’s start our exploration with the Natality dataset. The graph above charts share of teenager births, comparing to grand total by year. Between 1969, nominal number of births by teenagers went up from 307,561 to 441,110. However, this is not necessarily a bad news, considering growing US population. While in 1973, almost every fifth birth (19.55%) was by a teenager mother, by 2005 this ratio dropped to every 10th birth (10.18%.) To pull relevant source data, we simply need to run the following query (which would incidentally retrieve preteen births as well [outliers representing fewer than 200 births a year.]):
Calculate your age, WITHOUT using Excel’s DateDif function.
My previous post on using Excel’s DATEDIF function resulted in rather productive discussions on LinkedIn. First of all, some users are concerned that the DATEDIF function will no longer be supported in Excel and will cease to exist at some point in the future. Microsoft openly states that “this function is provided for compatibility with Lotus 1-2-3.” , and given the history of this support, I don’t see why it will be discontinued. Nonetheless, I will make an attempt to replace DATEDIF function with other functions readily available in the program. Secondly, as a reminder that English is not the only language in the world, Microsoft created “local” versions of this function. A user confirmed existence of the SIFECHA function, but the syntax to use it, involves using semicolons, instead of commas. If you are using Spanish version of Excel, you might want to try both versions:
According to this site , remaining versions of this function include: