When you use a spreadsheet for things where the timing is essential, you likely include dates and times. Google Sheets offers a collection of functions for formatting, converting, and calculating dates and times to help with your data entry.
You might include dates for customer orders or budgeting and times for hours worked or time spent on a project. Because dates and times can come in all forms, you may need to manipulate these elements to display correctly in your sheet.
You may have a list of birthdates, order dates, or similar where you want to extract only the day, month, or year. For this, you can use the same named functions: DAY, MONTH, and YEAR.
The syntax for each is the same as
YEAR(date) where the argument can be a cell reference, date, or number.
Here, we have a date in cell D1 and use the following formulas to return the day, month, and year:
As another example, we have our date formatted as a number within the formula:
Similar to getting parts of a date, you can obtain parts of a time. Using HOUR, MINUTE, and SECOND, you can get the hours, minutes, and seconds from a time entry.
The syntax for each of these time functions is also the same as
SECOND(time) where the argument can be a cell reference, time, or number.
Here, we have a time in cell D1 and use these formulas to get the hours, minutes, and seconds.:
You can also enter a time into the formula within quotes and obtain your results with the following:
Maybe the dates and times in your sheet reside in separate cells. For example, you may have the day, month, and year for a date in three different cells or the hours, minutes, and seconds for a time in separate cells. You can combine the cells to create a complete date or time.
The syntax for each function’s formula is
DATE(year, month, day) and
TIME(hours, minutes, seconds) noting the date formula requires the year first, then month and day.
Using the following formula, you can combine cells A2, B2, and C2 to form a complete date:
To assemble a date in the correct format, you can enter the year, month, and day in the formula like this:
With this formula, you can combine cells A2, B2, and C2 to form a complete time:
To assemble a time in the correct format, you can enter the hours, minutes, and seconds in the formula as follows:
One way that you may want to work with dates in your sheet is to find the number of days, months, or years between two dates. You can use the DATEDIF function to find one of these three units.
The syntax for the function is
DATEDIF(start, end, unit) where you’ll enter the start and end dates in quotes or use cell references. For the
unit argument, you’ll enter a letter corresponding to the unit you want to find such as D for days, M for months, or Y for years, each in quotes.
In this first example, we’ll get the number of months between our dates in cells A2 and B2 with this formula:
To use the same start and end dates but include them in the formula instead, you’d use this formula:
Rather than finding any type of day, you might want just workdays. You can use NETWORKDAYS to find this number and also account for holidays.
The syntax is
NETWORKDAYS(start, end, holidays) where
holidays is an optional argument that references a cell range or array of dates.
To find the number of workdays between our dates in cells A2 and B2 without holidays, you’d use this formula:
To use the same cells containing dates but add the holidays in the cell range E2 through E5, you’d use this formula:
Calculating “business days,” or workdays, is something you might want to estimate a delivery, notice, or deadline. You can use the WORKDAY function to accomplish this.
The syntax is
WORKDAY(start, number_days, holidays) where you can optionally include a cell range of dates for
holidays like the function above.
To see the end date 10 workdays (B2) after our date in cell A2, you’d use this formula:
To see the end date for that same date 10 workdays later but also consider the holidays in cells E2 through E5, use this formula:
Two final functions you’ll find useful in Google Sheet are TODAY and NOW. With TODAY, you can see the current date and with NOW, you can see the current date and time. These cells update accordingly each time you open your sheet.
Neither function contains arguments, but you must include the parentheses for each.
To display the current date in cell A1, enter the following:
To display the current date and time in cell A1, enter this formula instead:
Working with dates and times in Google Sheets is easy with these functions. Whether you need to grab part of a time, find the number of months between two dates, or always display the current date and time when you open your sheet, you’re covered.