By the end of this guide, you’ll learn about the essential Date Functions of Google sheets and mastering the art of utilizing them to calculate the number of working days in a project before its due date or days until the birthday of your special one.
The DATE function of Google sheets will return a date you enter or its serial number by combining a set of the individual day, month, and year elements entered as function’s arguments.
Quick Tip: These instructions are used for Google Sheets and may not function well for MS Excel.
When you combine DATE with Google sheets functions, you can generate a variety of date formulas. One of the most important uses of the DATE function is to ensure that the sheets interpret the data correctly, especially when data is not entered in the most used format.
The DATE function combines the elements including year, day, month from different locations in the worksheet and ensures that dates picked from the sheet are in number data and not in the text.
Dates in the Google sheets are stored as sequential serial numbers.
Fact Check: The serial number represents the number of days elapsed since December 31, 1899.
You can use the DATEVALUE function to convert any date input (different format or text) to convert it into number data that represents the date. To understand better here’s an example:
DATEVALUE (“Jan 1”) and DATEVALUE (“01-Jan-2021”) return the same serial number (44197) though the inputs have vast differences in the format.
To return the current date on a cell, use TODAY ( ) and use the function NOW ( ) to return the current date and time. Both the functions don’t require any arguments.
Quick Tip: The Date inserted will auto-update as the cell in the Google sheet is changed.
The function NOW ( ) functions the same as TODAY, the only difference is NOW returns the current and time. Function NOW ( ) + 2 returns current date plus two days, and NOW ( ) -9/24 returns the current date and time 9 hours ago since 1 = 24 hours.
We can use the function YEAR ( ), MONTH ( ), and DAY ( ) to extract the year, month and day of the date passed in the argument.
While the function DAYS ( ) calculates the number of days between the 2 dates. To understand the functioning, it internally calculates the DATEVALUE of the first date and the DATEVALUE of the second date calculating the difference between the two.
To calculate the number of months between two dates, you can use the DATEDIF ( ) function, with a third argument set to M. To understand, consider this example:
=DATEDIF ( “July 1, 1982”, TODAY ( ), “M” ) returns the number of months between July 1982 and today.
Similarly, the function YEARFRAC ( ) calculates the number of years between the two dates.
The function EDATE ( ) is used to calculate a date, specified number of months before or after mentioned date. Consider this example to understand:
When you use EDATE ( TODAY ( ), -1 ), it returns the date that is one month before the current date.
To calculate the last date of the given month, use the function EOMONTH ( ). EOMONTH (TODAY ( ), -1 ) returns the value as the last date of the previous month. Now, add 1 to the result, =EOMONTH ( TODAY ( ), -1) +1 and it will return the first date of the current month.
The WEEKDAY ( ) function returns the day of the week corresponding to the date with Sunday represented as the first day of the week when the second argument is set to 1. Now, set the second argument to 3, and the days of the week will be numbered starting with Tuesday.
Use the function WORKDAY ( ) to calculate the date that is a specified number of days before or after a date mentioned, not including weekends. For example, WORKDAY ( Today (), -7 ) returns a date value that is 7 working days before the current date.
Similarly, the function NETWORKDAYS ( ) calculates the number of working days between two dates mentioned as arguments. You can also combine it with EOMONTH to calculate the number of working days that are still left to the end of the current month:
=NETWORKDAYS ( TODAY () ), EOMONTH ( TODAY ( ), 0) )
Also Read: Bulk IP Address Lookup with Google Sheets
| Task | Working Formula |
| Add number of days to a date | =A1 + 5 |
| Get a day that is 6 months prior to a date | =EDATE(A1, -5) |
| Add number of years to a date | =DATE(YEAR(A1) + 5, MONTH(A1), DAY(A1)) |
| Difference in days between two dates | =DAYS(A1, A2) |
| Total working days between two dates | =NETWORKDAYS(A1, A2) |
| Get a date that is 10 working days from now | =WORKDAY(TODAY(), 10) |
| Get the total number of months between two dates | =DATEIF(A1, A2, “M”) |
| Get the difference in years between two dates | =DATEIF(A1, A2, “Y”) |
| Get the number of days in the current month | =EOMONTH(TODAY(), 0) – (EOMONTH(TODAY(), -1) + 1) |
| Print the day of the week | =TEXT(TODAY(), “ddddd”) |
| Calculate the age in years | =ROUNDDOWN(YEARFRAC(A1, TODAY(), 1)) |
| Days until your next birthday | =DAYS(DATE(YEAR(A1)+DATEDIF(A1,TODAY(),”Y”)+1, MONTH(A1),DAY(A1), TODAY()) |
| Months and days between two dates | =DATEDIF(A1,A2,”YM”)&” months, “&DATEDIF(A1,A2,”MD”)&” days” |
We hope this guide helps you understand the complete use of DATE functions in Google sheets. Stick to this corner to learn more!
Leave a Reply