10.10. Using Common Functions: Working with Dates and Times in Google Sheets
Electronic spreadsheets are a powerful tool for managing data and information. Google Sheets, in particular, offers a variety of functions that make it easier to work with numbers, text, and especially dates and times. In this chapter, we'll explore some of the most common and useful functions for manipulating dates and times in Google Sheets.
Understanding the Date and Time Format in Google Sheets
Before we dive into specific functions, it's important to understand how Google Sheets handles dates and times. Internally, Google Sheets stores dates as sequential numbers known as "date values", where each day is a whole number, starting from December 30, 1899, which is considered day 1. Times are stored as decimal fractions , where 0.5 represents half a day, or 12 hours.
To format a cell as a date or time, you can go to the "Format" menu and select "Number" and then "Date", "Time" or "Date and Time". This will cause the value in the cell to be displayed according to the chosen format.
Basic Functions for Dates and Times
Now, let's explore some of the most common functions for working with dates and times:
- SUM: Although normally used to add numbers, the SUM function can also be used to add dates and times. For example, if you want to calculate the date that is 10 days after a specific date, you can use
=SUM(start_date; 10). - AVERAGE: Similarly, the AVERAGE function can be used to find the average date among multiple dates.
- TODAY: Returns the current date. The function has no arguments and is automatically updated when the spreadsheet is reloaded.
- NOW: Returns the current date and time. Just like the TODAY function, it updates automatically.
Working with Dates
Some specific functions for working with dates include:
- DAY: Returns the day of a date. For example,
=DAY("2023-03-15")will return 15. - MONTH: Returns the month of a date.
=MONTH("2023-03-15")will return 3. - YEAR: Returns the year of a date.
=YEAR("2023-03-15")will return 2023. - DATEIF: Calculates the difference between two dates. For example,
=DATEDIF(start_date; end_date; "D")will calculate the number of days between dates. - ESDATEDIF: Similar to DATADIF, but with support for more types of intervals.
- DATE: Creates a date from year, month and day values. For example,
=DATE(2023; 3; 15)will return 03/15/2023.
Working with Hours
When it comes to hours, you can use functions like:
- TIME: Returns the time of a time value. For example,
=TIME("13:45:00")will return 13. - MINUTE: Returns the minutes of a time value.
=MINUTE("13:45:00")will return 45. - SECONDS: Returns the seconds of a time value.
=SECOND("13:45:30")will return 30. - TIME: Creates a time value from hours, minutes and seconds. For example,
=TIME(13; 45; 0)will return 13:45.
Practical Examples
To better illustrate, here are some practical examples:
- Calculate age from date of birth:
=DATEDIF(date_birth; TODAY(); "Y")will calculate how many full years have passed from the date of birth to today. - Find the number of working days between two dates:
=TOTAL WORKDAYS(start_date; end_date)will return the number of working days, excluding weekends and holidays (which can be specified as an additional range). - Add a specific number of business days to a date:
=WORKDAY(start_date; days)will calculate the end date after adding the specified number of working days to the start date.
Final Considerations
Working with dates and times in Google Sheets may seem complex at first glance, but by using the right functions, you can perform a wide range of calculations and analyzes efficiently. Remember to always check cell formatting to ensure that dates and times are being interpreted correctly by Sheets. With practice, you will become increasingly adept at manipulating these types of data, making your spreadsheets more dynamic and informative.
This chapter provided an overview of how you can use common functions to work with dates and times in Google Sheets. With thesetools in hand, you are ready to create more complex and useful spreadsheets, whether for personal or professional use. Keep exploring and practicing to fully master using dates and times in your spreadsheets.
