9.7. Creating and Using Basic Formulas: Working with Dates and Times
Working with dates and times is one of the most common and essential features in spreadsheets. Google Sheets, as a robust spreadsheet tool, offers a wide range of formulas and functions that help you manipulate, calculate and analyze information related to dates and times. In this chapter, we'll explore how to create and use basic formulas to work with these types of data.
Understanding Dates and Times in Google Sheets
In Google Sheets, dates are treated as serial numbers, where each day is represented by a whole number, starting from December 30, 1899, which is considered day 1. Hours are represented as decimal fractions of the day. For example, 12:00 PM (noon) is represented as 0.5 since it is half of the day.
Inserting Dates and Times
To enter a date or time, you can simply type the date or time in the desired format into a cell. Google Sheets will automatically recognize and format the cell as a date or time. For example:
- Dates: 01/01/2021, January 1, 2021
- Times: 1:00 PM, 1:00 PM
Basic Formulas for Dates
Let's start with some basic formulas for working with dates:
- TODAY() - Returns the current date.
- NOW() - Returns the current date and time.
- DAY(date) - Returns the day of a date.
- MONTH(date) - Returns the month of a date.
- YEAR(date) - Returns the year of a date.
- DATEDIF(start_date, end_date, "unit") - Calculates the difference between two dates.
For example, to calculate a person's age, you can use the DATEIF function with the unit "Y" for years:
=DATEIF(A2, TODAY(), "Y")
Where A2 contains the person's date of birth.
Basic Formulas for Hours
For hours, we also have some useful functions:
- TIME(time) - Returns the time of a time value.
- MINUTE(time) - Returns the minute of a time value.
- SECOND(time) - Returns the second of a time value.
If you want to calculate time worked based on a check-in and check-out time, you can simply subtract the check-in time from the check-out time:
=B2 - A2
Where A2 is the entry time and B2 is the exit time.
Formatting Dates and Times
To format dates and times, you can select the cell or range of cells and use the "Format" menu and then "Number" to choose the desired date and time format. There are a variety of options available, including regionally specific formats.
Operations with Dates and Times
In addition to basic formulas, you can perform operations with dates and times, such as adding or subtracting days, months or years from a specific date. This can be done using the DATA function:
=DATE(YEAR(A2), MONTH(A2) + 3, DAY(A2))
This formula adds 3 months to the date in A2.
To add hours, minutes or seconds to a time, you can use:
=A2 + (hours / 24) + (minutes / 1440) + (seconds / 86400)
Where A2 is the starting time and you replace "hours", "minutes" and "seconds" with the values you want to add.
Advanced Date and Time Functions
Google Sheets also offers more advanced functions for working with dates and times, such as:
- EOMONTH(date, months) - Returns the last day of the month after adding a specific number of months to the given date.
- NETWORKDAYS(start_date, end_date) - Calculates the number of working days between two dates.
- EDATE(date, months) - Returns the same date in a different month, adding the specified number of months.
These functions are particularly useful for financial and business calculations, such as finding an invoice due date or calculating a project deadline.
Final Considerations
Working with dates and times may seem complicated initially, but with practice and an understanding of the functions available in Google Sheets, you can manipulate this data efficiently to meet your needs. Remember to practice the formulas presented and explore the formatting options to present your data in a clear and informative way.
With this knowledge, you are now better equipped to create more dynamic and useful spreadsheets, making the most of Google Sheets' features related to dates and times.
