DAYS Function – Google Sheets

The DAYS function is a simple date function that lets you calculate the number of days between two dates. The same result can arrived at by simply subtracting two dates such as: ="7/31/2016"-"6/5/1991".

Purpose

The DAYS function returns the number of days between two dates.

Related functions

DATEDIF – Calculate length of time between two dates in years, months, and/or days.

MINUS – Subtract one value from another. Can be done with dates.

Errors

#NUM – The inputs are numbers but are not valid dates. This could happen if you used the 35th day of November, “11/352018”.

#VALUE! – The inputs are something that doesn’t convert to a number such as “The other day” or “Yester-yester-day”.

Syntax

=DAYS(end_date,start_date)

Note that, for some reason, the function wants the ending date first, then the starting date.

Video Explanation

Examples

A few simple examples of the DAYS function.

Formula Description Result
=DAYS("7/31/2017","7/30/2016") Number of days between 7/30/2016 and 7/31/2017 366
=DAYS("1/7/2020","12/5/2015") Number of days between 12/5/2015 and 1/7/2020 1,494
=DAYS(C3,"2/9/1994") Number of days between 3/5/2001 (assuming that the value was in C3) and 2/9/1994 -2,581

 Live examples in Sheets

Go to this spreadsheet for examples of the DAYS function that you can study and use anywhere you would like.

WORKDAY Function – Google Sheets

The WORKDAY function calculates a workday a given number of days away from a date. You can optionally specify a list of holidays to exclude from the count. If your days, months, and years are in different cells, use the DATE function to combine them.

Syntax

=WORKDAY(start_date,days,[holidays])

  • start_date – The date from which to start counting the days.
  • days – The number of days to add to the start_date
  • [holidays] – Optional specification of which days are to be excluded from the count of work days.

Related functions

WEEKDAY – Returns the week number for a given date.

DAYS – Find the number of days between two dates.

NETWORKDAYS.INTL – Calculate the number of working days in between two days and exclude specified days as weekends.

NETWORKDAYS – Calculate the number of working days in between two days and exclude weekends.

WORKDAY.INTL – Like this function but with movable weekends.

Errors

#NUM – The inputs are numbers but are not valid dates. This could happen if you used the 35th day of November, “11/35/2018”.

#VALUE! – The inputs are something that doesn’t convert to a number such as “The other day” or “Yester-yester-day”.

 Live examples in Sheets

Go to this spreadsheet for the examples of the WORKDAY function that you can study and use anywhere you would like.

YEAR Function – Google Sheets

The YEAR function is a simple date function that extracts just the year from a time value. If you are working with times, but only care about the years, the YEAR function lets you get rid of everything else. Remember that dates are really just saved as numbers by your spreadsheet with date formatting applied.

Purpose

The YEAR function returns the value of the year from any given unit of time.

Video Explanation

Syntax

=YEAR(time)

Examples

Example 1 – Plain and Simple

A few simple examples of the YEAR function.

Formula Description Result
=YEAR("1/2016") Extract the year value from 1/2016. 2016
=YEAR("1/31/2016") Extract the year value from 1/31/2016. 2016
=YEAR("2016") Extract the year value from 2016 1905

Tip: Notice that the YEAR function can’t figure out that the number 2016, when typed alone in a cell, refers to a year. See how dates and times work to make a little sense out of this.

Example 2 - Separating the values of a date and time.

Different date values examined using various functions.

Time value Tickmark YEAR MONTH DAY HOUR MINUTE SECOND Formatted as number
7/30/20161201673000042,581.00
7-30-20162201673000042,581.00
6/1/2017 2:32 PM32017611432042,887.61
July 31, 20164201673100042,582.00
12:30 PM518991230123000.52
4/11/2017 11:24:146201741111241442,836.48
4/11/20177201741100042,836.00
 818991230000 
19189912310001.00
0.2510189912306000.25
4300011201792200043,000.00
  • 1 A random date typed in using slashes.
  • 2 A random date typed in using dashes. Note that it is processed the same way as the date with slashes.
  • 3 Typed a random date in and added a time. Now notice the hours and minutes being extracted.
  • 4 Typed in a date but spelled out. This also works, but it won't work if you write the day as "31st" instead if 31.
  • 5 If you only type in a time, the "zero" date is 12/30/1899.
  • 6 Entered with the NOW function. This will change constantly in the linked Gooogle Sheet.
  • 7 Entered with the TODAY function. This will change daily in the this linked Gooogle Sheet.
  • 8 A blank cell is December 30, 1899. Similar to tickmark 5.
  • 9 Typing a 1 increments the value in number 8 above by 1 day. In other words "1=12/31/1899". It is just a matter of how it is displayed which can be controlled by going to the Format menu and choosing Number.
  • 10 Just a decimal with no whole number increments the value by hours, minutes and seconds instead of days.
  • 11 To get near the present date, you need to start with 43,000 days away from 12/30/1899.

 Live examples in Sheets

Go to this spreadsheet for the examples of the YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND functions shown above that you can study and use anywhere you would like.

MONTH Function – Google Sheets

The MONTH function is a simple date function that extracts just the month from a given time. If you are working with times, but only care about the months, the MONTH function lets you get rid of everything else. Remember that dates are really just saved as numbers by your spreadsheet with date formatting applied.

Purpose

The MONTH function returns the value of the month from any given unit of time.

Video Explanation

Syntax

=MONTH(time)

Examples

Example 1 – Plain and Simple

A few simple examples of the MONTH function.

Formula Description Result
=MONTH("10/5/2016") Extract the month value from 10/5/2016. 10
=MONTH("October") Return of the of the month of October. #VALUE!
=MONTH("October 8") Extract the month value from October 8 10

Tip: Notice that the MONTH function can’t figure out that what you mean by “October” unless you put a day value in front of it. See how dates and times work to make a little sense out of this.

Example 2 - Separating the values of a date and time.

Different date values examined using various functions.

Time value Tickmark YEAR MONTH DAY HOUR MINUTE SECOND Formatted as number
7/30/20161201673000042,581.00
7-30-20162201673000042,581.00
6/1/2017 2:32 PM32017611432042,887.61
July 31, 20164201673100042,582.00
12:30 PM518991230123000.52
4/11/2017 11:24:146201741111241442,836.48
4/11/20177201741100042,836.00
 818991230000 
19189912310001.00
0.2510189912306000.25
4300011201792200043,000.00
  • 1 A random date typed in using slashes.
  • 2 A random date typed in using dashes. Note that it is processed the same way as the date with slashes.
  • 3 Typed a random date in and added a time. Now notice the hours and minutes being extracted.
  • 4 Typed in a date but spelled out. This also works, but it won't work if you write the day as "31st" instead if 31.
  • 5 If you only type in a time, the "zero" date is 12/30/1899.
  • 6 Entered with the NOW function. This will change constantly in the linked Gooogle Sheet.
  • 7 Entered with the TODAY function. This will change daily in the this linked Gooogle Sheet.
  • 8 A blank cell is December 30, 1899. Similar to tickmark 5.
  • 9 Typing a 1 increments the value in number 8 above by 1 day. In other words "1=12/31/1899". It is just a matter of how it is displayed which can be controlled by going to the Format menu and choosing Number.
  • 10 Just a decimal with no whole number increments the value by hours, minutes and seconds instead of days.
  • 11 To get near the present date, you need to start with 43,000 days away from 12/30/1899.

 Live examples in Sheets

Go to this spreadsheet for the examples of the YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND functions shown above that you can study and use anywhere you would like.

YEARFRAC Function – Google Sheets

The YEARFRAC function takes two dates and returns the number of years, including fractions of a year, between them. You can optionally specify different methods of counting days. Remember that dates are really just saved as numbers by your spreadsheet with date formatting applied. The date must be in a valid format, so you have to use the DATE function, wrap it in quotes, or use a cell reference to a cell containing a date.

Syntax

=YEARFRAC(start_date,end_date,[day_count_convention])

  • start_date – The date for the calculation to start on.
  • end_date – The date for the calculation to end on.
  • day_count_convention – Optional indication of day count method to use.
    • 0 [or blank] – US (NASD) 30/360
    • 1 – actual/actual Uses the actual number of days.
    • 2 – actual/360 Uses the actual number of days but with a 360 day year.
    • 3 – actual/365 Uses the actual number of days but assumes a 365 day year. Doesn’t recognize leap year.
    • 4 – European 30/360 Similar to 0 but uses European standards instead of US.

Related functions

DATE – Takes separate year, month, and day values and returns them as a date

DATEVALUE – Returns the serial value of a date

DAY – Returns the value the day from a given date

DAYS360 – Calculates interest using the 360 method

Errors

#NUM – An input is a number but is not a valid date. This could happen if you used the 35th day of November.

#VALUE! – The input is something that doesn’t convert to a number such as “The other day” or “Yester-yester-day”.

Examples

 Live examples in Sheets

Go to this spreadsheet for the examples of the YEARFRAC function shown above that you can study and use anywhere you would like.

DAY Function – Google Sheets

The DAY function is a simple date function that extracts just the day from a given time. If you are working with times, but only care about the day, the DAY function lets you get rid of everything else.

Before reading the examples below, ensure you know how dates and times work.

Purpose

The DAY function returns the value of the month from any given unit of time.

Video Explanation

Syntax

=DAY(time)

Examples

Example 1 – Plain and Simple

A few simple examples of the DAY function.

Formula Description Result
=DAY("1/2016") Extract the day value from 1/2016. 1
=DAY("1/31/2016") Extract the day value from 1/31/2016. 31
=DAY("1") Extract the day value from 1. 31

Tip: The DAY function shows its eccentricity when trying to convert the number 1 to a day. See example 9 in Example 2 below. It helps to keep in mind how dates and times work to make a little sense out of this.

Example 2 - Separating the values of a date and time.

Different date values examined using various functions.

Time value Tickmark YEAR MONTH DAY HOUR MINUTE SECOND Formatted as number
7/30/20161201673000042,581.00
7-30-20162201673000042,581.00
6/1/2017 2:32 PM32017611432042,887.61
July 31, 20164201673100042,582.00
12:30 PM518991230123000.52
4/11/2017 11:24:146201741111241442,836.48
4/11/20177201741100042,836.00
 818991230000 
19189912310001.00
0.2510189912306000.25
4300011201792200043,000.00
  • 1 A random date typed in using slashes.
  • 2 A random date typed in using dashes. Note that it is processed the same way as the date with slashes.
  • 3 Typed a random date in and added a time. Now notice the hours and minutes being extracted.
  • 4 Typed in a date but spelled out. This also works, but it won't work if you write the day as "31st" instead if 31.
  • 5 If you only type in a time, the "zero" date is 12/30/1899.
  • 6 Entered with the NOW function. This will change constantly in the linked Gooogle Sheet.
  • 7 Entered with the TODAY function. This will change daily in the this linked Gooogle Sheet.
  • 8 A blank cell is December 30, 1899. Similar to tickmark 5.
  • 9 Typing a 1 increments the value in number 8 above by 1 day. In other words "1=12/31/1899". It is just a matter of how it is displayed which can be controlled by going to the Format menu and choosing Number.
  • 10 Just a decimal with no whole number increments the value by hours, minutes and seconds instead of days.
  • 11 To get near the present date, you need to start with 43,000 days away from 12/30/1899.

 Live examples in Sheets

Go to this spreadsheet for the examples of the YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND functions shown above that you can study and use anywhere you would like.

MINUTE Function – Google Sheets

The MINUTE function is a simple date function that extracts just the minute from a given time. If you are working with times, but only care about the minutes, the MINUTE function lets you get rid of everything else. Remember that dates are really just saved as numbers by your spreadsheet with date formatting applied.

Purpose

The MINUTE function returns the number of minutes from any given unit of time.

Video Explanation

Syntax

=MINUTE(time)

Examples

Example 1 – Plain and Simple

A few simple examples of the MINUTE function.

Formula Description Result
=MINUTE("1:02") Extract the minute value from 1:02. 2
=MINUTE("1:02 PM") Extract the minute value from 1:02 PM. 2
=MINUTE("2.75") Extract the minute value from 2.75. 0
=MINUTE("2.56849") Extract the minute value from 2.56849. 38

Tip: Sheets is interpreting a whole number and decimal as a number of days and a portion of a day. Therefore, 2.75 is 2 days and 18 hours. Thus, there are no minutes. See how dates and times work to make a little sense out of this.

Example 2 - Separating the values of a date and time.

Different date values examined using various functions.

Time value Tickmark YEAR MONTH DAY HOUR MINUTE SECOND Formatted as number
7/30/20161201673000042,581.00
7-30-20162201673000042,581.00
6/1/2017 2:32 PM32017611432042,887.61
July 31, 20164201673100042,582.00
12:30 PM518991230123000.52
4/11/2017 11:24:146201741111241442,836.48
4/11/20177201741100042,836.00
 818991230000 
19189912310001.00
0.2510189912306000.25
4300011201792200043,000.00
  • 1 A random date typed in using slashes.
  • 2 A random date typed in using dashes. Note that it is processed the same way as the date with slashes.
  • 3 Typed a random date in and added a time. Now notice the hours and minutes being extracted.
  • 4 Typed in a date but spelled out. This also works, but it won't work if you write the day as "31st" instead if 31.
  • 5 If you only type in a time, the "zero" date is 12/30/1899.
  • 6 Entered with the NOW function. This will change constantly in the linked Gooogle Sheet.
  • 7 Entered with the TODAY function. This will change daily in the this linked Gooogle Sheet.
  • 8 A blank cell is December 30, 1899. Similar to tickmark 5.
  • 9 Typing a 1 increments the value in number 8 above by 1 day. In other words "1=12/31/1899". It is just a matter of how it is displayed which can be controlled by going to the Format menu and choosing Number.
  • 10 Just a decimal with no whole number increments the value by hours, minutes and seconds instead of days.
  • 11 To get near the present date, you need to start with 43,000 days away from 12/30/1899.

 Live examples in Sheets

Go to this spreadsheet for the examples of the YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND functions shown above that you can study and use anywhere you would like.

SECOND Function – Google Sheets

The SECOND function is a simple date function that returns the number of seconds from a given time. If you are working with times, but only care about the seconds, the SECOND function lets you get rid of everything else. Remember that dates are really just saved as numbers by your spreadsheet with date formatting applied.

Purpose

The SECOND function returns the number of seconds from any given unit of time.

Video Explanation

Syntax

=SECOND(time)

Examples

Example 1 – Plain and Simple

A few simple examples of the SECOND function.

Formula Description Result
=SECOND("2:20:20") Extract the second value from 2:20:20. 20
=SECOND("1.2") Extract the second value from 1.2. 0
=SECOND("2.22224545") Extract the second value from 2.22224545. 2

Tip: Notice that the SECOND function returns a zero for 1.2. This is because .2 happens to be exactly 12 minutes. See how dates and times work to make a little sense out of this.

Example 2 - Separating the values of a date and time.

Different date values examined using various functions.

Time value Tickmark YEAR MONTH DAY HOUR MINUTE SECOND Formatted as number
7/30/20161201673000042,581.00
7-30-20162201673000042,581.00
6/1/2017 2:32 PM32017611432042,887.61
July 31, 20164201673100042,582.00
12:30 PM518991230123000.52
4/11/2017 11:24:146201741111241442,836.48
4/11/20177201741100042,836.00
 818991230000 
19189912310001.00
0.2510189912306000.25
4300011201792200043,000.00
  • 1 A random date typed in using slashes.
  • 2 A random date typed in using dashes. Note that it is processed the same way as the date with slashes.
  • 3 Typed a random date in and added a time. Now notice the hours and minutes being extracted.
  • 4 Typed in a date but spelled out. This also works, but it won't work if you write the day as "31st" instead if 31.
  • 5 If you only type in a time, the "zero" date is 12/30/1899.
  • 6 Entered with the NOW function. This will change constantly in the linked Gooogle Sheet.
  • 7 Entered with the TODAY function. This will change daily in the this linked Gooogle Sheet.
  • 8 A blank cell is December 30, 1899. Similar to tickmark 5.
  • 9 Typing a 1 increments the value in number 8 above by 1 day. In other words "1=12/31/1899". It is just a matter of how it is displayed which can be controlled by going to the Format menu and choosing Number.
  • 10 Just a decimal with no whole number increments the value by hours, minutes and seconds instead of days.
  • 11 To get near the present date, you need to start with 43,000 days away from 12/30/1899.

 Live examples in Sheets

Go to this spreadsheet for the examples of the YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND functions shown above that you can study and use anywhere you would like.

EDATE Function – Google Sheets

The EDATE function takes a starting date and a number as its input. It then increments (or decrements) the date by the number of months that you specify.

This formula can be useful due to the fact that not all months have the same number of days. Additionally, February has a different number of days depending on the year. The EDATE function accounts for all of these differences. If the number of months that you provide has decimals, it is truncated instead of rounded so keep it as a whole number.

Dates require special care when being used in formulas as they don’t behave like regular numbers do in spreadsheets.

Video Explanation

Purpose

The EDATE function add or subtracts a given number of months to a given date.

Syntax

=EDATE(start date,number of months)

  • start date – The starting date from which the calculation begins. Remember how dates work in formulas so that you know how to enter this date.
  • number of months – The number of months by which to increase/decrease the starting month

Examples

Example 1 – Plain and Simple

A simple example of the EDATE function.

Formula Description Result
=EDATE(“5/17/2017”,13) Add 13 months to 5/17/2017 6/17/2018
=EDATE(“5/17/2017”,-2) Subtract 2 months from 5/17/2017 3/17/2017

The EDATE function can go forward and backward in time.

Example 2 – Knowing the number of days in a month

This example shows how the EDATE function knows the number of days in each month. This takes away the chore of manually adding the different numbers of days.

Note that this function is DATE and not EDATE.
Formula Description Result
=EDATE(“5/17/2017”,3) Add 3 months to 5/17/2017 8/17/2017
=DATE(2017,5,17)+31+30+31 Add 3 months to 5/17/2017 8/17/2017

Note how much easier it is to use the EDATE function.

Example 3 – Days in a leap year

The EDATE function knows the number of days in a leap year. Note the two dates highlighted below that are one day different.

Formula Description Result
=EDATE(“2/17/2015”,1) Add 1 month to 2/17/2015 3/17/2015
=EDATE(“2/17/2016”,1) Add 1 month to 2/17/2016 3/17/2016
=DATE(2015,2,17)+28 Add 28 days to 2/17/2015 3/17/2015
=DATE(2016,2,17)+28 Add 28 days to 2/17/2016 3/16/2016

 

 Live examples in Sheets

Go to this spreadsheet for the examples of the EDATE function shown above that you can study and use anywhere you would like.

EOMONTH Function – Google Sheets

EOMONTH is a function in Google Sheets that is typically used to return values for use in other functions rather than for using by itself. You are already going to know the ending day of most given months. However, if dates are changing in different parts of your spreadsheet, the last day of the month could be changing as well which would be quite difficult to nail down without this formula.

Remember when using the EOMONTH function that dates behave differently than numbers, and therefore you cannot type the date directly into the formula. You will need to use quotes, the DATE function, or a reference to a cell containing a valid date value.

Video Explanation

Purpose

Returns the end date of a month that is a specified number of months away from a starting date

Syntax

=EOMONTH(start_date,number_of_months)

start_date Any day during a starting month. 1/3/2017 and 1/6/2017 would both give the same output.

number_of_months Number of months to go forward or backward

Tip: The value for the number of months will be truncated instead of rounded if you use a decimal. For example, 3.7 will be changed to 3 instead of 4.

Examples

Example 1 – Plain and Simple

A few examples of the EOMONTH function. Notice that the values returned are the end of each month. If you want to increment by months and stay at the same day of the month, consider using the EDATE Function function.

Formula Description Result
=EOMONTH(“5/17/2017”,13) Return the last day of the month 13 months after May 17, 2017 6/30/2018
=EOMONTH(“5/17/2017”,-13) Return the last day of the month 13 months before May 17, 2017 4/30/2016

The examples show EOMONTH increasing and decreasing the month.

Example 2 – Calculating employment benefit start dates

Many times, benefits start after an employee is at a company for a certain number of calendar months. It does not matter when during a month the employee started, only how many whole months have elapsed. Below is a table that calculates the day after which the employee becomes eligible for benefits.

A B C
1 Hire Date Formula Day after which eligibility starts
2 1/13/2017 =EOMONTH(A2,3) 4/30/2017
3 1/30/2017 =EOMONTH(A3,3) 4/30/2017
4 12/21/2016 =EOMONTH(A4,3) 3/31/2017

Even though the three employees started within days of each other, one of them has a different eligibility date.

 

 Live examples in Sheets

Go to this spreadsheet for the examples of the EOMONTH function shown above that you can study and use anywhere you would like.