Quick Links
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.
Find the Day, Month, or Year From a Date
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
DAY(date)
,
MONTH(date)
, and
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:
=DAY(D1)
=MONTH(D1)
=YEAR(D1)
As another example, we have our date formatted as a number within the formula:
=DAY(29418)
=MONTH(29418)
=YEAR(29418)
Get the Hours, Minutes, or Seconds From a Time
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 HOUR(time), MINUTE(time), and 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.:
=HOUR(D1)
=MINUTE(D1)
=SECOND(D1)
You can also enter a time into the formula within quotes and obtain your results with the following:
=HOUR("10:41:25")
=MINUTE("10:41:25")
=SECOND("10:41:25")
Combine Cells to Create a Date or Time
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:
=DATE(A2,B2,C2)
To assemble a date in the correct format, you can enter the year, month, and day in the formula like this:
=DATE(1996,11,4)
With this formula, you can combine cells A2, B2, and C2 to form a complete time:
=TIME(A2,B2,C2)
To assemble a time in the correct format, you can enter the hours, minutes, and seconds in the formula as follows:
=TIME(11,23,14)
Count the Number of Days, Months, or Years Between Dates
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:
=DATEDIF(A2,B2,"M")
To use the same start and end dates but include them in the formula instead, you'd use this formula:
=DATEDIF("1/1/2021","12/31/2022","M")
Calculate the Number of Workdays Between Dates
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:
=NETWORKDAYS(A2,B2)
To use the same cells containing dates but add the holidays in the cell range E2 through E5, you'd use this formula:
=NETWORKDAYS(A2,B2,E2:E5)
Find the Date After a Number of Workdays
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:
=WORKDAY(A2,B2)
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:
=WORKDAY(A2,B2,E2:E5)
Display the Current Date and Time
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:
=TODAY()
To display the current date and time in cell A1, enter this formula instead:
=NOW()
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.