google sheets date - Free Excel Tutorial

April 2, 2022

Add Months To Date In Google Sheets

It is important to adjust time periods when performing financial modeling. In Google Sheets, you can use the MONTH function to add a specific number of months to a date. The MONTH function takes one argument: the number of months you want to add. Here will show you how to add months to date in Google Sheets and even provide some examples that might be useful!

Method1: Add Months to Date using EDATE Function

Generic formula:

=EDATE(Sart_date, months)

Arguments

Start_date: the starting date on which you want to add months
Months: the month that you wish to add

Return Value:

The function will return the date that is the indicated number of months after the start_date. If you enter a negative number of months, it’ll count back from the end date.

When you enter the MONTH function, Google Sheets looks at the date in the cell that you specify as the start_date argument. It then adds the number of months that you entered as the second argument to that date. If you enter a negative number for the second argument, Google Sheets subtracts that number of months from the start_date.

To add a number of months is placed at B2 and add months to the date in cell A2. The formula for this would be:

=EDATE(A1,B1)

Or simply enter the number you want in your formula, and it will be replaced with that value.

=EDATE(A1,10)

Explanation

This will add 10 months to the date in cell A1. The function will return the date that is the indicated number of months after the start_date. If you enter a negative number of months, it’ll count back from the end date.

The EDATE function is a great way to find out your next date. Simply enter any valid date and then select how many months away from now you want the new one! It’s really easy; just feed it with an amount less than or equal to the number of desired days between these two points-and voila.

Method2: Add Months to Date using Date Function

Generic formula:

=DATE(YEAR(date),MONTH(date))+months,DAY(date)))

Arguments

Date: The starting date on which you want to add months.
Months: Months are important when adding or subtracting a certain number of months from/to dates. They add the indicated amount, depending on whether it’s positive and negative values respectively; for example, -1 means one month less than what you want (in other words: ago).
YEAR(date): The year component of the date in Date.
MONTH(date): The month component of the date in Date.
DAY(date): The day component of the date in Date.

Return Value:

A date is the indicated number of months after the start_date. If you enter a negative number of months, it’ll count back from the end date.

How This Formula Work

To add months to the date in cell A1, and a number of added is placed at B1. The formula for this would be as follows:

=DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))

With the Year, Month, and Day functions, you can easily create your date from any combination of these three values. With the Date function, you can create dates based on any year and month of your choice.

Related Functions

Google Sheets DAY function
The Google Sheets DAY function returns a day of a date (from 1 to 31).The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)…
Google Sheets EDATE function
The Google Sheets EDATE function returns the serial number that represents the date that is a specified number of months before or after a specified date.The syntax of the EDATE function is as below:=EDATE (start_date, months)…
Google Sheets DATE function
The Google Sheets DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…
Google Sheets MONTH Function
The Google Sheets MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12.The syntax of the MONTH function is as below:=MONTH (serial_number)…
Google Sheets YEAR function
The Google Sheets YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…

November 30, 2021

Google Sheets WORKDAY Function

This post will guide you how to use Google Sheets WORKDAY function with syntax and examples.

Description

The Google Sheets WORKDAY function returns a date or a serial number that represents a date that is the indicated number of working days before or after the starting date you specified. You can add a specified number of working days to the starting date and then returns a serial date. The working days will exclude weekends and any dates specified as holidays.

You can use the WORKDAY function to calculate working days and non-working days.

The WORKDAY function can be used to get a number representing the week of the year where the provided date falls in google sheets. The purpose of this function is to get the week number for a given date and it will return a number between 1 and 54.

The WORKDAY function is a build-in function in Google Sheets and it is categorized as a Date function.

Syntax

The syntax of the WORKDAY function is as below:

=WORKDAY(start_date, days, [holidays])

Where the WORKDAY function arguments are:

Start_date –This is a required argument. The starting date from which you want to count the number of working days. The date should be typed as a valid time a serial date.
days – This is a required argument. The number of working days that you want to add. A positive value for days yields a future date; a negative value yields a past date.
holidays – This is an optional argument. The list of holidays that you want to exclude from the working days. It can be a range of cells that contain the holiday dates or it can be a list of serial numbers that represent the holiday dates.

Note:

If any argument is not a valid Excel date, a #VALUE! Error is returned.
If start date value plus days is an invalid date, the WORKDAY function returns #NUM! Error.
A serial date is how the google sheets stores dates and it represents the number of days since 1900-01-01, so the January 1, 1900 date is serial number 1 by default.
If days value is not an integer number, it will be truncated.

Google Sheets WORKDAY Function Examples

The below examples will show you how to use google sheets WORKDAY Function to return the working days from the start date.

1# get the date 50 workdays from the starting date “3/20/2021”, enter the following formula in Cell B1.

=WORKDAY(DATE(2021,3,20),50)

2# get the date 50 workdays from the starting date “3/20/2021”, excluding holidays 5/1/2021, type the following formula in Cell B2.

=WORKDAY(DATE(2021,3,20),50,DATE(2021,5,1))

November 14, 2021November 14, 2021

Google Sheets NETWORKDAYS.INTL Function

This post will guide you how to use Google Sheets NETWORKDAYS.INTL function with syntax and examples.

Description

The Google Sheets NETWORKDAYS.INTL function returns the number of net working days between tow given days excluding specified weekend days and holidays.

The NETWORKDAYS.INTL function can be used to calculate the number of working days between dates in google sheets. And NETWORKDAYS.INTL will exclude weekends optionally and provides a way to specify which days of the week should be considered as weekends.

The purpose of this function is to get the number of working days between two dates and its returned values is a number that representing days.

This function is more complex that the NETWORKDAYS function because it allows you to control which days of the week are considered weekends.

The NETWORKDAYS.INTL function is a build-in function in Google Sheets and it is categorized as a Date function.

Syntax

The syntax of the NETWORKDAYS.INTL function is as below:

=NETWORKDAYS.INTL (start_date, end_date, [weekend], [holidays])

Where the NETWORKDAYS.INTL function argument is:

Start_date -This is a required argument. The starting of date from which to calculate the number of working days.
End_date – this is a required argument. The ending of date from which to calculate the number of working days.
Weekend – this is an optional argument. A number that representing which days of the week are considered as weekends.
Holidays – this is an optional argument. A list of non-working days.

Note:

Weekend argument can be specified using seven 0’s and 1’s, where the first number represents Monday and the last number represents Sunday. A 0’s means that the day is a working day, and a 1’s means that the day is a weekend. For example, “0000011” means that Saturday and Sunday are weekends.
Weekend argument also can be specified as a single number, you can refer to the below table displaying the weekend numbers and their corresponding weekend days.

Weekend Number	Weekend Days
1 or omitted	Saturday and Sunday
2	Sunday and Monday
3	Monday and Tuesday
4	Tuesday and Wednesday
5	Wednesday and Thursday
6	Thursday and Friday
7	Friday and Saturday
11	Sunday only
12	Monday only
13	Tuesday only
14	Wednesday only
15	Thursday only
16	Friday only
17	Saturday only

NETWORKDAYS.INTL function only calculate all workdays, ignoring any time values.
NETWORKDAYS.INTL function will exclude all Saturday and Sunday automatically.
NETWORKDAYS.INTL function must be included both the starting date and ending date when calculating workdays.
Holidays argument must be a date serial number values, or a date value returned by N function, or returned by DATE, DATEVALUE functions. It should be standard date values or date serial numbers.
If start_date is greater than end_date, NETWORKDAYS.INTL function will return a negative value.
If start_date or end_date are out of range, NETWORKDAYS.INTL function will return the #NUM! error.

Google Sheets NETWORKDAYS.INTL Function Examples

The below examples will show you how to calculate working days between two given dates, taking into account the specified weekends and holidays in google sheets.

#1 Calculate working days between two dates in range B1:B2 by excluding default weekends with weekend string value, see the below formula:

=NETWORKDAYS.INTL(B1,B2,"1000000",C1:C4) //returns 295, exclude holidays, and Monday is weekend

The weekend string value “1000000” means that Monday is the weekend, and all other days are considered as working days.

November 14, 2021November 14, 2021

Google Sheets NETWORKDAYS Function

This post will guide you how to use Google Sheets NETWORKDAYS function with syntax and examples.

Description

The Google Sheets NETWORKDAYS function returns the number of net working days between tow given days.

The NETWORKDAYS function can be used to calculate the number of working days between dates in google sheets. And NETWORKDAYS will exclude weekends automatically. The purpose of this function is to get the number of working days between two dates and its returned values is a number that representing days.

The NETWORKDAYS function is a build-in function in Google Sheets and it is categorized as a Date function.

Syntax

The syntax of the NETWORKDAYS function is as below:

=NETWORKDAYS (start_date, end_date, [holidays])

Where the NETWORKDAYS function argument is:

Start_date -This is a required argument. The starting of date from which to calculate the number of working days.
End_date – this is a required argument. The ending of date from which to calculate the number of working days.
Holidays – this is an optional argument. A list of non-working days.

Note:

NETWORKDAYS function only calculate all workdays, ignoring any time values.
NETWORKDAYS function will exclude all Saturday and Sunday automatically.
NETWORKDAYS function must be include both the starting date and ending date when calculating workdays.
Holidays argument must be a date serial number values, or a date value returned by N function, or returned by DATE, DATEVALUE functions. It should be standard date values or date serial numbers.
If you wish to use other days of week as the weekend, you can use the NETWORKDAYS.INTL function.

Google Sheets NETWORKDAYS Function Examples

If you want to calculate the number of workdays between two dates in google sheets, you can use a formula based on the NETWORKDAYS function. And it will exclude all weekend days between the start dates and end dates. You need to specify two dates contain start date and end date so that you can get the number of workdays.

For example, you have two dates in range B1:B2, and you want to get the workdays between these two dates, you just need to use the following formula:

=NETWORKDAYS(B1,B2)

Type this formula in a blank Cell, and press Enter key in your keyboard.

The above formula contains the NETWORKDAYS function but does not take into account holidays, if you wish to take into account holidays( a range in C1:C4), just using the following formula:

=NETWORKDAYS(B1,B2,C1:C4)

November 13, 2021

Google Sheets MONTH Function

This post will guide you how to use Google Sheets MONTH function with syntax and examples.

Description

The Google Sheets MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. And you can use the MONTH function to get a month component from a given date value. And you can feed the month number to another formula.

The MONTH function can be used to extract the month from a given date as a number between 1 to 12 in google sheets. The purpose of this function is to get MONTH as a number from a date and its returned values is a number between 1 and 12.

The MONTH function is a build-in function in Google Sheets and it is categorized as a Date function.

Syntax

The syntax of the MONTH function is as below:

=MONTH (date)

Where the MONTH function argument is:

Date -This is a required argument. A valid date value that contains the month component you want to extract. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE (2021,3,15) for the 15th day of March, 2021.

Note:

You need to make sure that the Date argument is a cell reference that containing a date, or a function which returns a date value or a date serial number.
If Date argument is not recognized, MONTH function will return #VALUE! Error message.
If Date argument is supplied as a date number that is out of range, MONTH function will return #NUM! error message.
You can use the MONTH function to extract a month component from a given date, or using this function to feed a month number into another function to create a more complex formula like the DATE function.
Date argument can be supplied to the MONTH function as a date text string (e.g. “2021/11/13” or “Nov-11-2021”). Or creating a date value with separate year, month, and day by using the DATE function.

Google Sheets MONTH Function Examples

The below examples will show you how to use google sheets MONTH Function to return an integer number that representing the month component of a given date.

1# get month of the date in Cell A1, enter the following formula in Cell B1.

=MONTH(A1)

2# convert Month name to number, type the following formula in Cell B1.

You should use the DATEVALUE function to convert a date stored as text to a serial number. Then feed the result into the MONTH function to extract a month number from date.

=MONTH(DATEVALUE(A1 & "1"))

November 5, 2021

Google Sheets DATE Function

This post will guide you how to use Google Sheets DATE function with syntax and examples.

Description

The Google Sheets DATE converts a year, month, and day into a date.

The DATE function can be used to create a valid date from individual year, month and day value in google sheets. The purpose of this function is to create a date with year, month and day and its returned values is a valid date.

The DATE function is a build-in function in Google Sheets and it is categorized as a Date function.

Syntax

The syntax of the DATE function is as below:

= DATE (year, month, day)

Where the VALUE function argument is:

Text -This is a required argument. The year component of the date
Month – This is a required argument. The month component of the date
Day – This is a required argument. The day component of the date

Note:

If the year value is between 0 and 1899, google sheets will add that value to 1900 to determine the year.
If the year value is between 1900 and 9999, google sheets will use that value as the year value.
If year is less than 0 or is greater than 9999, google sheets will return the error value: #NUM!
If month is greater than 12, every 12 months will add 1 year to the year value.
If the day value is greater than the number of days in the month specified, then it will add that number of days to the first day in the month. For example: DATA(2017,6,32), the DATE function will return “7/2/2017”

Google Sheets DATE Function Examples

The below examples will show you how to use google sheets DATE Function to create a date.

#1 You can use the DATE function to create the dates with hard-coded numbers, just do the following formula:

=DATE(2021,11,15) //it returns “11/15/2021”