Working with date and times in a spreadsheet.

There's quite a lot date and time in data. Knowledge of how to manipulate date and time can be very beneficial. Dates and times are made of seconds,minutes,Hours and Days. The representation in spreadsheets is not too different.

Dates in Spreadsheets.

Dates in spreadsheets are represented by serial numbers different from regular numbers. Operations like sorting,addition,subtraction on these numbers do not provide expected results. According to Microsoft, serial number 1 represents 1st January 1900 at 12:00:00 am. Increasing or decreasing these serial numbers produces different dates.The number 30010 represents 28th February, 1982. There are several functions for manipulating date in spreadsheet.

Time in Spreadsheets

Times are stored in spreadsheets as decimal numbers that goes from .0 to .99999, .0 represents 00:00:00 while .99999 is 23:59:59. As in dates, increasing and reducing these numbers will produce different times.

Manipulating Dates and Times

 

Above is the format that represents date and time in Spreadsheets. The 0 represents a digit. The representation is DD/MM/YY HH:MM:SS. YY can also be YYYY. The software checks the 1st two digits, if the number is equal to or less than 31, that becomes the day value, it then checks the next two, if the number is equal or less than 12, that's the month value. The last two or four becomes the year value. The software will recognize any subset from the above mentioned number pattern.

'12-03' typed into a cell will be interpreted by the software as 12th of March. '03/2004' will be March of 2004.

Did you know that?

Gives

Date and Time functions in Spreadsheets

  

There are a lot functions in spreadsheet that manipulate date and time. This web page from Microsoft discusses all these functions in details. A few thing to note about these functions:

1. Most functions work by splitting up components that make up the date and time in spreadsheets. For instance 'HOUR' function takes the hour function in date and time for calculation.
2. Functions can be combined or 'nested' with other function to achieve objectives whilst calculating.

 

Above is an example of nested functions to get the serial date for the last day of the current year.

Enjoy working with dates and times.