Using Dates and Times with Microsoft Excel 2016
Microsoft Office Excel’s date and time functions are one of the most common data types that people work with. Unfortunately, they are also the most frustrating. This is especially true if you are still new to Excel.
This is because the creators of Excel decided to use a serial number for date representation, instead of our traditional day, month, year, (dd/MM/yyyy) not to mention hours, minutes, and seconds (HH-mm). This is made even more complex by the fact that dates also correspond with specific days of the week, like Tuesday or Saturday, even though that information isn’t explicitly stored in cells.
Configuring Date and Time Settings
First and foremost, you want to alleviate any future frustration by ensuring that your date and time set correctly for your time zone and preferences.
This can be accomplished through the Region tab of your Control Panel. Here you change your Display Language, Date and Time and Date Formats to match your computer or your preferences. Date formatting will include preferences such as your week starting on a Sunday or Monday and how many digits to be displayed for a year. Choose from a set of default options or mix and match to fit your needs.
Working with Date and Times
Simply put, Excel’s date and time serial numbers that intuitive to use. Fortunately, Excel utilizes designated functions that make it a bit easier to use and find times and dates. Beginning with DATE and TIME, here is the following syntax:
=TIME (hours, minutes, seconds)
=DATE (year, month, day)
For each function, specify the hours, minutes and seconds, or year, month, and day as numbers. For example, January 1, 2018 can be entered as follows:
=Date (2018,1,1)
This date will be stored as 43099. This is technically the number of days since January 1, 1900 and represents the date January 1, 2018.
For times, 6:00 AM can be entered as:
=Time (6,0,0)
This will be stored as 0.25. This number represents how many of the day has passed, in this case, 25% of a 24 hour day period.
If we want to represent both a specific date and a specific time we would simple add both of these functions together. For example, 6AM on January 1, 2018 can be entered as:
=Date (2018,1,1) + Time (6,0,0)
Excel will calculate this as 43099.25. This number means that Excel is storing the specific date and time that we want.
Summary
Understanding how Microsoft Office Excel stores information is an essential part of working with dates and times. However, the successful functioning and formatting of this information the way that you want it to is largely down to your default settings.
Having a strong knowledge over the way that this information is stored and how your default settings may affect this information is key to being able to manipulate your settings for various results and use cases. Remember, if you aren’t seeing something that you expect to, revert back to your settings.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
