When doing any data processing work, it’s almost inevitable that you will at some point encounter a dataset that contains dates, times, or both. Most spreadsheet programs behave similarly as far as dates & times are concerned, but what of Spreadsheet.com? Do you need to be aware of any nuances? Well, let’s find out! You can follow along by making your own copy of the 🔗template used in this tutorial.
Contents
Video Explanation
Date Formats
By default, Spreadsheet.com accepts most conventional formats and will immediately recognize dates upon entry. For example, let’s take a look at the following dataset:
The data to work with is in the region below the header row. Column B contains various date & time formats, and Spreadsheet.com recognizes them as such. The alignment of the numbers to the right is evidence of this. Remember, spreadsheets align text values to the left by default, and they align date/number values to the right. We can take a look at each format individually:
- 7/30/2021 – This takes on the structure, “dd/mm/yyyy,” without a leading zero for the day value.
- July 30, 2021 – The month and day are still treated as a date value if you spell out the month and day.
- 44407 – Now, this is where it gets a little tricky. To understand this, we should first agree on one spreadsheet fundamental truth: “All dates are numbers/all dates can be converted to numerical values.” What do we mean by this?
Dates and Times Under the Hood
How would you count the number of days between two days? You’d have to reduce this into an arithmetic problem. Therefore, all dates would need to have a corresponding numerical value that we can then use to find the difference. So, what number corresponds to what date?
1899 – The year of the sheet?
For reasons beyond the scope of this article, spreadsheet programmers decided to treat the years before 1899 (1900 for MS Excel) as non-existent. Therefore, as far as Spreadsheet.com is concerned, the world began on December 31, 1899. Hence December 31, 1899, is equal to the numerical value 1. 12/30/1899 would be equal to 0, whereas 1/1/1900 would translate to 2. Days before12/30/1899 would, of course, take on negative values-you get the idea, right?
Now that it’s clear that each date value has a numerical equivalent let’s go back to the value that kickstarted this short history lesson: 44407. How would you know what day corresponds to this value? Click on the cell, go to Format > Data type… > Date, and click on “Update.”
The spreadsheet updates the value to a date:
Time as a Numeric Value
If spreadsheet.com stores a date in the form of an integer value, then it only makes sense that spreadsheet.com represents the time the same way. However, if the value of a day increments by one every day, then time has to be a number less than 1, in other words, a fraction/decimal.
The number 44407 translates to 7/30/2021 at exactly 00:00:00. However, what if we wanted to represent 7/30/2021 at 12:00:00?
It’s simple. There are 24 hours in a day. 12:00:00 means 12 hours have elapsed. So the fraction of a day that has elapsed is 12/24 = 0.5. The numeric value will then be 44407+0.5 = 44407.50. If we ran =HOUR(44407.50), the output would be 12.
Date and Time Formats
Spreadsheet.com can store time values on their own or appended to dates. Cells B11 and B12 contain date-time values, while cell B19 in the original cell contains time values without dates. A time value must be in the format “hh:mm:ss” for spreadsheet.com to recognize it.
Functions Involving Dates
=TODAY()
The TODAY() function dynamically returns the current day. You can manipulate the output format as desired by going to Format > Data type… > Format.
=NOW()
NOW() dynamically returns the current day and time. You can change the format as we did with the TODAY() function.
- =
YEAR(input)
The YEAR function returns the year in which the specified date falls. For example, if we wrote =YEAR(B9), the output would be 2021 since the day in B9 falls under 2021.
Here’s a fun trick. We can use the YEAR function to give us the year of a numerical value. For example,
=YEAR(44407) yields 2021.
- =
MONTH(input)
The MONTH function is similar to the YEAR function, but it returns the month of the specified date.
- =
DAY(input)
Works in the same way as YEAR and MONTH but returns the DAY of the month specific to that date.
Functions Involving Time
These functions are similar to the date functions, but the outputs are either the hours, minutes or seconds involving a particular date. The functions are =HOUR(input), =MINUTE(input) and =SECOND(input).
Things to Note About Dates and Times:
- If you apply any of the date/time functions to a blank cell, the output will be as if the cell contained 12/30/1999 00:00:00. For instance, the function
=YEAR(empty cell)would return 1899.
- If you apply any of the time functions to a cell that contains a time value but no date, Spreadsheet.com will assume that the specified time exists on December 30, 1899.
It would be interesting to dig deep into why 12/30/1899 is the beginning moment for spreadsheets. Interesting food for thought, isn’t it?