Quick Links
When you use a spreadsheet for things where the timing is essential, you likely include dates and times. Google Sheets offers acollection of functionsfor formatting, converting, and calculating dates and times to help with your data entry.
You might include dates for customer orders orbudgetingand 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
,
, and
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 ourdate formattedas a number within the formula:
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 asHOUR(time),MINUTE(time), andSECOND(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:
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’re able to combine the cells to create a complete date or time.
Related:How to Create a Custom Date or Time Format in Google Sheets
The syntax for each function’s formula isDATE(year, month, day)andTIME(hours, minutes, seconds)noting the date formula requires the year first, then month and day.
Using the following formula, you’re able to combine cells A2, B2, and C2 to form a complete date:
To assemble a date in the correct format, you may 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 atime in the correct format, you can enter the hours, minutes, and seconds in the formula as follows:
Count the Number of Days, Months, or Years Between Dates
One way that you may want to work with dates in your sheet is tofind the number of days, months, or years between two dates. you’re able to use the DATEDIF function to find one of these three units.
The syntax for the function isDATEDIF(start, end, unit)where you’ll enter the start and end dates in quotes or use cell references. For theunitargument, 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:
Calculate the Number of Workdays Between Dates
Rather than finding any type of day, you might want just workdays. You can use NETWORKDAYS tofind this numberand also account for holidays.
Related:How to Find the Number of Days Between Two Dates in Microsoft Excel
The syntax isNETWORKDAYS(start, end, holidays)whereholidaysis 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:
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 isWORKDAY(start, number_days, holidays)where you’re able to optionally include a cell range of dates forholidayslike 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:
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 cansee 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 timesin 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.
Related:13 Microsoft Excel Date and Time Functions You Should Know
