# Making the complete Use of Date Functions in Google Sheets

August 27, 2021 Twinkle Kapoor

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) )

## Use these Google Sheet Date Formulas for Common Scenarios

We hope this guide helps you understand the complete use of DATE functions in Google sheets. Stick to this corner to learn more!

## Author

• 