Google Sheets: The Complete Guide to All Date Functions

Understanding DATE functions is crucial for mastering most data manipulation techniques, as time is indispensable. Whether summarizing data based on date grouping, plotting charts, or highlighting expiry dates, date functions play a crucial role in various analytical tasks.

Welcome to our comprehensive guide on mastering all available date functions in Google Sheets under one roof.

TODAY Function

When you want to display the current date in a cell, use this function.

=TODAY()

Remember, the date inserted will recalculate based on changes in the sheet. You can control the recalculation by navigating to File > Settings > Calculation.

NOW Function

The NOW function in Google Sheets is similar to the TODAY function. While the TODAY function returns only the current date, the NOW function returns the current date and time. It’s important to note that both functions are volatile, meaning they update continuously, and the recalculation settings that apply to TODAY also apply to NOW.

=NOW()

DAY Function

Use the DAY function to return the day component of a given date or timestamp.

Syntax:

=DAY(date)

Argument:

date – The date from which to extract the day.

Examples:

=DAY(TODAY()) // will return the day component from today's date
=DAY(A1) // will return the day component from the given date in cell A1.

If cell A1 is empty, the formula will return 30, because the value in an empty cell is 0, and dates are stored as serial numbers, where 0 is equivalent to 30/12/1899.

How do you return a blank with the DAY function if the cell is empty?

Wrap the cell reference with the DATEVALUE function and the DAY function within an IFERROR function as follows:

=IFERROR(DAY(DATEVALUE(A1)))

MONTH Function

The MONTH function returns the month component of a given date or timestamp in Google Sheets.

Syntax:

=MONTH(date)

Arguments:

date – The specific date from which to extract the month.

Examples:

=MONTH(TODAY()) // will return the month component from today's date
=MONTH(A1) // will return the month component from the given date in cell A1.

If cell A1 is empty, the formula will return 12, for the reason already cited under the DAY function.

How do you return a blank with the MONTH function if the cell is empty?

Wrap the cell reference with the DATEVALUE function and the MONTH function within an IFERROR function as follows:

=IFERROR(MONTH(DATEVALUE(A1)))

YEAR Function

The YEAR function returns the year component from a given date or timestamp.

Syntax:

=YEAR(date)

Arguments:

date – The date from which to extract the year.

Examples:

=YEAR(TODAY()) // will return the year component from today's date
=YEAR(A1) // will return the year component from the given date in cell A1.

The year component of an empty cell will be 1899, as explained under the DAY function.

How do you return a blank with the YEAR function if the cell is empty?

Wrap the cell reference with the DATEVALUE function and the YEAR function within an IFERROR function as follows:

=IFERROR(YEAR(DATEVALUE(A1)))

DATE Function

Use the Google Sheets DATE function to convert a provided year, month, and day into a date. This function is often used in the criteria part of formulas like SUMIF, COUNTIF, etc.

Syntax:

=DATE(year, month, day)

Arguments:

year: The year component of the date.
month: The month component of the date.
day: The day component of the date.

Example:

=DATE(2024, 1, 25) // will return the date 25/01/2024.

DATEVALUE Function

We have already used the DATEVALUE function with DAY, MONTH, and YEAR above to address the blank cell issue.

The purpose of the DATEVALUE function in Google Sheets is to convert a provided date string in a known format into a date value.

Syntax:

=DATEVALUE(date_string)

Arguments:

date_string: The string representing the date.

Examples:

=DATEVALUE(TODAY()) // will return the date serial number of today's date.
=DATEVALUE("30/12/1899") // will return 0.
=DATEVALUE("31/12/1899") // will return 1.
=DATEVALUE(A1) // will return the date value of the date in cell A1. If A1 is empty, it will return a #VALUE! error.

DAYS Function

You can utilize the DAYS function to find the difference in the number of days between two given dates.

Syntax:

=DAYS(end_date, start_date)

Arguments:

end_date: The end of the date range.
start_date: The start of the date range.

Assume cell A1 contains 20/05/2024 and cell B1 contains 25/05/2024. The following formula will return 5:

=DAYS(B1, A1)

This is equivalent to using =B1-A1.

How do you return a blank with the DAYS function if either cell is empty?

Wrap the cell references with the DATEVALUE function and the DAYS function within an IFERROR function as follows:

=IFERROR(DAYS(DATEVALUE(B1), DATEVALUE(A1)))

DAYS360 Function

The purpose of the DAYS360 function is to calculate the difference between two dates based on a 360-day year, primarily used in financial interest calculations.

Syntax:

DAYS360(start_date, end_date, [method])

Arguments:

start_date: The start date of the range.
end_date: The end date of the range.
method: Day count method indicator (optional).

Examples:

The method in these formulas is 1, which represents the European method. In this method, start dates and end dates that fall on the 31st day of a month are considered the 30th day of the same month.

=DAYS360("25/03/2018", "31/03/2018", 1) // returns 5
=DAYS360("29/02/2024", "01/03/2024", 1) // returns 2
=DAYS360("28/02/2023", "01/03/2023", 1) // returns 3

The ‘method’ in these formulas is 0, which represents the U.S. method.

=DAYS360("25/03/2018", "31/03/2018", 0) // returns 6
=DAYS360("30/03/2018", "31/03/2018", 0) // returns 0
=DAYS360("29/02/2024", "01/03/2024", 0) // returns 1
=DAYS360("28/02/2023", "01/03/2023", 0) // returns 1

In this method:

If the start_date is the last day of a month, it is considered the 30th day of the same month. Please refer to the last two formulas.
If the end_date is the 31st day of the month and the start_date is earlier than the 30th, the end_date is changed to the 1st day of the next month (as in the first formula above); otherwise, the end_date is changed to the 30th day of the same month (as in the second formula above).

To address the empty cell issue in the DAYS360 function, follow the IFERROR and DATEVALUE approach as follows:

=IFERROR(DAYS360(DATEVALUE(A1), DATEVALUE(B1), 1))

EDATE Function

The EDATE function in Google Sheets returns a date that is a specified number of months before or after a provided start date.

Syntax:

=EDATE(start_date, months)

Arguments:

start_date: The initial date from which to calculate the new date.
months: The number of months before (-) or after (+) the start_date to calculate.

Examples:

=EDATE(A1, 5)
=EDATE(A1, -5)

Assume cell A1 contains the date 10/01/2024. The first formula will return the date 10/06/2024, whereas the second formula will return the date 10/08/2023.

If cell A1 is empty, the formula =EDATE(A1, 5) will return 30/05/1900, whereas =EDATE(A1, -5) will return a #NUM! error.

Use the DATEVALUE and IFERROR functions with EDATE to return a blank cell when the start date is empty:

=IFERROR(EDATE(DATEVALUE(A1), 5))

Then format the result to Format > Number > Date.

ISOWEEKNUM Function

Returns the number of the ISO week of the year for a given date.

Syntax:

=ISOWEEKNUM(date)

To understand this Google Sheets function, you should first know what an ISO Week is. This Wikipedia article provides detailed information about ISO Weeks.

ISO Week in a Nutshell:

The ISOWEEKNUM function follows the ISO 8601 date and time standard.
In this standard, weeks begin on Monday and end on Sunday.
According to this standard, Week #1 of the year is the week that contains the first Thursday of the year.

Please refer to the table below.

Date	Weekday	ISO Week #	Remarks
1/1/21	Fri	53
2/1/21	Sat	53
3/1/21	Sun	53
4/1/21	Mon	1
5/1/21	Tue	1
6/1/21	Wed	1
7/1/21	Thu	1	First Thursday
8/1/21	Fri	1
9/1/21	Sat	1
10/1/21	Sun	1
11/1/21	Mon	2

The ISOWEEKNUM function will return 52 as the week number if the referenced cell is empty. To solve this, follow the example below:

=IFERROR(ISOWEEKNUM(DATEVALUE(A1)))

NETWORKDAYS Function

You can utilize the NETWORKDAYS function in Google Sheets to get the working days between two dates.

Syntax:

=NETWORKDAYS(start_date, end_date, [holidays])

Arguments:

start_date: The starting date of the period.
end_date: The ending date of the period.
holidays: [Optional] A range or array constant containing the dates to consider as holidays.

When returning the result, the formula excludes dates that fall on Saturday, Sunday, and any specified holidays.

Please refer to the image below for an example.

Examples of the NETWORKDAYS function in Google Sheets

To return an empty cell when either the start or end date is empty in the NETWORKDAYS function in Google Sheets, you can adjust the formula as follows:

=IFERROR(NETWORKDAYS(DATEVALUE(A2), DATEVALUE(B2), C2:C11))

NETWORKDAYS.INTL Function

The NETWORKDAYS.INTL function is similar to the NETWORKDAYS function, with the additional capability of allowing you to specify which days of the week are considered weekends.

Syntax:

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

Arguments:

start_date: The starting date of the period.
end_date: The ending date of the period.
weekend: [Optional – 1 by default] A number or string specifying weekends.

Number Method:

Weekend #	Weekend
1	Saturday & Sunday
11	Sunday
2	Sunday & Monday
12	Monday
3	Monday & Tuesday
13	Tuesday
4	Tuesday & Wednesday
14	Wednesday
5	Wednesday & Thursday
15	Thursday
6	Thursday & Friday
16	Friday
7	Friday & Saturday
17	Saturday

String Method:

This method specifies weekends using seven 0s and 1s, representing Monday to Sunday. 1 represents weekends and 0 represents workdays.

“0000000” represents all working days, while “0000011” represents Saturday and Sunday as weekends.

holidays: [Optional] A range/array containing the dates to consider as holidays.

Examples:

Assume cell A1 contains 01/01/2024 and B1 contains 31/12/2024. The holidays are 01/01/2024 and 25/12/2024 in C1:C2.

=NETWORKDAYS(A1, B1, C1:C2)
=NETWORKDAYS.INTL(A1, B1, "0000011", C1:C2)
=NETWORKDAYS.INTL(A1, B1, 1, C1:C2)

All the above formulas will return 260, excluding the two holidays and the Saturday & Sunday weekends.

With the NETWORKDAYS.INTL function, you can specify weekends of your choice.

Regarding handling empty cell issues in NETWORKDAYS.INTL, please follow the same DATEVALUE and IFERROR approach we adopted in NETWORKDAYS.

Example:

=IFERROR(NETWORKDAYS.INTL(DATEVALUE(A1), DATEVALUE(B1), "0000011", C1:C2))

WORKDAY Function

We can use the WORKDAY function in Google Sheets to get the end date after a specified number of working days, excluding Saturday and Sunday weekends and selected holidays.

Syntax:

=WORKDAY(start_date, num_days, [holidays])

Arguments:

start_date: The date from which to begin counting.
num_days: The number of working days to advance (if positive) or backward (if negative).
holidays: [Optional] A range/array containing the dates to consider as holidays.

Example:

Assume cell A1 contains the date 24/05/2024, which falls on a Friday. The following formula will return 27/05/2024, which falls on a Monday.

=WORKDAY(A1, 1)

To exclude holidays in addition to weekends, enter the holidays in a range, for example, C1:C10, and specify that range in the formula.

=WORKDAY(A1, 20, C1:C10)

If the specified cell is empty, the WORKDAY formula will return an invalid date. To return an empty result, follow this:

=IFERROR(WORKDAY(DATEVALUE(A1), 20, C1:C10))

Apply the Format > Number > Date formatting to the result cell.

WORKDAY.INTL Function

You can use the WORKDAY.INTL function to return a date by advancing a specified number of workdays, excluding specific weekends and holidays.

Syntax:

=WORKDAY.INTL(start_date, num_days, [weekend], [holidays])

Arguments:

start_date: The date from which to begin counting.
num_days: The number of working days to advance (if positive) or backward (if negative).
weekend: [Optional – 1 by default] A number or string specifying weekends. See the NETWORKDAYS.INTL function above for details.
holidays: [Optional] A range/array containing the dates to consider as holidays.

Examples:

Cell A1 contains 24/05/2024, which falls on a Friday.

=WORKDAY(A1, 1) // will return 27/5/2024, Monday
=WORKDAY.INTL(A1, 1) // will return 27/05/2024, Monday
=WORKDAY.INTL(A1, 1, "0000001") // will return 25/05/2024, Saturday

In the last formula, only Sunday is considered a weekend. Additionally, you can specify holidays in these formulas.

=WORKDAY(A1, 20, C1:C10) // Saturday & Sunday are weekends
=WORKDAY.INTL(A1, 20, "0000011" ,C1:C10) // Saturday & Sunday are weekends
=WORKDAY.INTL(A1, 20, "0000001", C1:C10) // Sunday is a weekend

In these formulas, C1:C10 contains the holidays.

If the specified cell is empty, the WORKDAY.INTL formula will return an invalid date. To return an empty result, follow this:

=IFERROR(WORKDAY.INTL(DATEVALUE(A1), 20, "0000001", C1:C10))

Apply the Format > Number > Date formatting to the result cell.

YEARFRAC Function

The YEARFRAC function is another useful date function. It returns the number of years, including fractional years, between two given dates.

Syntax:

=YEARFRAC(start_date, end_date, [day_count_convention])

Arguments:

start_date: The start date of the date range.
end_date: The end date of the date range.
day_count_convention: [OPTIONAL – default is 0] The method used to calculate the fraction of a year.

The day_count_convention parameter determines how the fraction of the year is calculated, with the following options:

0: Indicates 30 days for each month, resulting in 360 days in a year (30/360).
1: Indicates the actual number of days between the two specified dates (actual/actual).
2: Indicates the actual number of days between the two specified dates but assumes a 360-day year (actual/360).
3: Indicates the actual number of days between the two specified dates but assumes a 365-day year (actual/365).
4: Similar to 0, but adjusts end-of-month dates according to European financial conventions (European 30/360).

Examples:

Also Refer:

Day count convention (External Source)

Note: To handle the empty cell issue in YEARFRAC, use the DATEVALUE and IFERROR combination, similar to most date functions in Google Sheets.

Here is one example:

=IFERROR(YEARFRAC(DATEVALUE(A1), DATEVALUE(B1), 1))

DATEDIF Function

The DAYS function returns the number of days between two dates, whereas the DATEDIF function returns the number of days, months, or years between two dates.

Syntax:

=DATEDIF(start_date, end_date, unit)

Arguments:

start_date: The start date to consider in the calculation.
end_date: The end date to consider in the calculation.
unit: A text abbreviation for the unit of time. The possible values are:
- "Y": Returns the number of whole years.
- "M": Returns the number of whole months.
- "D": Returns the number of days.
- "MD": Returns the number of days after subtracting whole months.
- "YM": Returns the number of whole months after subtracting whole years.
- "YD": Returns the number of days between the start date and the end date, assuming the start date and end date are no more than one year apart.

Examples:

In all the examples below, the date in cell A1 is 1-Jan-2024 and the date in cell B1 is 2-Feb-2025.

=DATEDIF(A1, B1, "Y")   // Returns 1 (whole year)
=DATEDIF(A1, B1, "M")   // Returns 13 (whole months)
=DATEDIF(A1, B1, "D")   // Returns 398 (days)
=DATEDIF(A1, B1, "MD")  // Returns 1 (days after subtracting whole months)
=DATEDIF(A1, B1, "YM")  // Returns 1 (whole months after subtracting whole years)
=DATEDIF(A1, B1, "YD")  // Returns 32 (days between start and end date within one year)

Note: If the start_date is empty, the DATEDIF function will return a #NUM error.

WEEKDAY Function

The WEEKDAY function in Google Sheets returns a number representing the day of the week for the provided date.

Syntax:

=WEEKDAY(date, [type])

Arguments:

date: The date for which to determine the day of the week.
type: [Optional – defaults to 1]
- 1: Numbering starts from 1 to 7, where 1 is Sunday and 7 is Saturday.
- 2: Numbering starts from 1 to 7, where 1 is Monday and 7 is Sunday.
- 3: Numbering starts from 0 to 6, where 0 is Monday and 6 is Sunday.

Examples:

A1 contains the date 01/01/2024, which falls on a Monday:

=WEEKDAY(A1, 1) // returns 2
=WEEKDAY(A1, 2) // returns 1
=WEEKDAY(A1, 3) // returns 0

To handle the scenario where an empty cell returns a weekday number of 5, 6, or 7 depending on the specified type, follow this workaround to return an empty cell instead:

=IFERROR(WEEKDAY(DATEVALUE(A1), 1))

WEEKNUM Function

Given a date input, the WEEKNUM function returns the week number in the year the date falls.

Syntax:

=WEEKNUM(date, [type]

Arguments:

date: The date that determines the week number.
type: [OPTIONAL – default is 1]. Types are 1, 2, 11 to 17, and 21. Here is the breakdown:

In the following types, the week containing January 1st is numbered week 1:

Type	Start	End
1	Sunday	Saturday
2	Monday	Sunday
11	Monday	Sunday
12	Tuesday	Monday
13	Wednesday	Tuesday
14	Thursday	Wednesday
15	Friday	Thursday
16	Saturday	Friday
17	Sunday	Saturday

In the ISO 8601 standard (type 21), the week containing the first Thursday of the year is numbered week 1.

Type	Start	End
21	Monday	Sunday

Examples:

In the following examples (screenshot), the following formulas in cells C2 and E2 are dragged/copied down:

=WEEKNUM(A2, 2) // C2 formula
=WEEKNUM(A2, 21) // E2 formula

Examples of the WEEKNUM function in Google Sheets

If the specified cell is empty, the WEEKNUM function will return 52 or 53, depending on the type specified. You can follow the example below to return an empty cell instead:

=IFERROR(WEEKNUM(DATEVALUE(A1), 1))

EOMONTH Function

The EOMONTH function returns the last date of the month that falls on the same month as the given date or a specified number of months before or after the given date.

Syntax:

=EOMONTH(start_date, months)

Arguments:

start_date: The date to convert to the end of the month date.
months: The number of months before or after the start_date.

Examples:

=EOMONTH(TODAY(), 0) // Returns the end of the month date in the current month
=EOMONTH(TODAY(), -1) // Returns the end of the month date in the previous month
=EOMONTH(TODAY(), 1) // Returns the end of the month date in the next month

If the ‘months’ argument is a positive number and the cell is empty, the EOMONTH function will return an unwanted result. To address this issue, follow this example:

=IFERROR(EOMONTH(DATEVALUE(A1), 5))

Apply Format > Number > Date to format the result as a date.

For more detailed information, you can refer to the tutorial: EOMONTH Function in Google Sheets: All About

Resources

72 COMMENTS

Erin December 23, 2023 At 1:53 am

Hello! I loved this and have saved it for future use. Unfortunately, what I was originally looking for isn’t on it, and I am hoping you might be able to help.

I have two sheets. One sheet has pay dates, and the second sheet is bills with their due dates. I want to auto-highlight the bills on sheet 2 that fall between one payday and the next from sheet 1. I know I can use a formula and manually add the two dates, but I’d like to have the formula refer to the actual cell since the dates will change every month.

I know it will be a custom formula, but I’m stuck on how to refer to a cell on the other sheet.

In my head, it goes like this:

If column F on sheet 2 is before cell A2 on sheet 1, then highlight the entire row on sheet 2.

Thank you!!!

- Prashanth KV December 23, 2023 At 9:59 am
  
  To refer to another sheet, you need to use INDIRECT in conditional formatting.
  
  Here is the rule that you can try, assuming the sheet names are Sheet1 and Sheet2:
  
  =AND(ISDATE($F1), LT($F1, INDIRECT("Sheet1!A2")))
  
Sydney B July 6, 2023 At 3:42 am

How can I pull the date from the month, year, and weekday? Think of a calendar view. I need to build a formula that pulls the month (January), year (2023), and weekday (Monday) to pull the date (January 2). I imagine that I need to include the week number somewhere, which is fine. I appreciate your help!

- Prashanth July 7, 2023 At 12:55 pm
  
  Assume that cells B2, B3, and B4 contain the month text, year, and weekday text, respectively.
  
  For example:
  
  Cell B2 contains the text “February”.
  Cell B3 contains the number 2023.
  Cell B4 contains the text “Monday”.
  
  Then you can try the following formula:
  
  =LET(mon,LET(dt,DATE(B3,MONTH(B2&1),1),dt-WEEKDAY(dt,2)+ MATCH(B4,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0)), IF(MONTH(mon)=MONTH(B2&1),mon,mon+7))
  
KenW February 1, 2023 At 2:50 am

I have a data set in a Google Sheet that captures employee time off requests for vacation, sick days, etc.

The data includes a Start Date column and an End Date column.

For example, someone is requesting vacation from 1/1/2023 to 1/15/2023.

I am trying to identify dupes (i.e., overlapping dates).

For example, if that same person enters another record later on for vacation from 1/10/2023 to 1/15/2023, we know that is impossible – they can’t be off “twice” on the same days.

What is the best way to capture those types of duplicates?

It’s not just the values on the columns, but the data range in between.

Thanks,
Ken

- Prashanth February 2, 2023 At 1:25 pm
  
  Hi, KenW,
  
  We can expand those dates using one of the new Lambda helper functions and find such duplicates.
  
  I have such a tutorial in the pipeline. If you share a sample of your Sheet, I may consider writing my formula/tutorial around that problem.
  
Elizabeth Hatch December 29, 2022 At 12:25 am

Hi!

I would like to create a header in my spreadsheet with the days of the month in increments of two days from 1-29, starting with the TODAY function; for 3 months.

I can do it for the first month by setting TODAY in the first cell and then adding two to the next cell over, copying it into the rest of the row up to 29.

Is there a function I can use to refer back to the first cell and start this sequence again in the next month (1-29), and then again in the third month?

Thanks for any help you can give.

- Prashanth December 29, 2022 At 5:25 pm
  
  Hi, Elizabeth Hatch,
  
  Give this array formula a try.
  
  Assume the first date is in cell A1.
  
  =lambda(final,filter(final,final<>""))({ifna(lambda(data, filter(data,data<=date(year(A1),month(A1),min(29,day(eomonth(A1,0)))))) (sequence(1,16,A1+2,2))),lambda(data,filter(data, data<=date(year(eomonth(A1,0)+1),month(eomonth(A1,0)+1), min(29,day(eomonth(eomonth(A1,0)+1,0))))))(sequence(1,16,eomonth(A1,0)+1,2)),lambda(data, filter(data,data<=date(year(eomonth(A1,1)+1), month(eomonth(A1,1)+1),min(29,day(eomonth(eomonth(A1,1)+1,0)))))) (sequence(1,16,eomonth(A1,1)+1,2))})
  
  It requires a blank row to populate the dates.
  
  - Prashanth December 29, 2022 At 6:23 pm
    
    The result must be formatted to Format > Number > Date.
    
Michelle L Adams November 3, 2022 At 2:07 pm

Hello! I would like to enter a date of a document that needs to be redone every 6 months.

At 5 months, I would like it to highlight yellow, and at the 6-month point, I would like it to be highlighted red.

Can I do this?

- Prashanth November 4, 2022 At 12:02 pm
  
  Hi, Michelle L Adams,
  
  Try these formulas.
  
  Yellow – =isbetween($F3,edate(today(),-6),edate(today(),-5),false)
  
  Red – =and(isdate($F3),lte($F3,edate(today(),-6)))
  
Zman September 20, 2022 At 10:40 am

Hi there,

I wonder if you can help me with my dilemma.

I have a list of dates and values.

I want to calculate the average values in a range for the last 7 “workdays” – a 7-day running average.

I can do it with the calculation of today -7 days, but I can’t get it to work with workday(cell ref, -7 workdays).

- Prashanth September 23, 2022 At 8:27 am
  
  Hi, Zman,
  
  Assume the criteria range (date) is column C (C1:C) and the average range is column B (B1:B).
  
  Then try this formula which uses the newly introduced MAP function.
  
  =AVERAGEIF(map(C1:C,lambda(wd,iferror(and(workday(wd-1,1)=wd,isbetween(wd,WORKDAY(today(),-7),today()))))),TRUE,B1:B)
  
B4ALLB4U August 8, 2022 At 1:07 pm

Hello, Thanks for your hard work.

I want to restrict users to select the date only three days from the current date.

If the user entered date is more than three days, the system has to show a pop-up error message and not accept the date in the column.

If the date is below three days, the system has to accept the date in the column.

Please guide me on how to set the above condition in Google sheets.

- Prashanth August 9, 2022 At 4:46 pm
  
  Hi, B4ALLB4U,
  
  You can do it as follows.
  
  Select the cells and go to Data > Data validation > Criteria > Date > On or before.
  
  Enter the following formula in the given field.
  
  =today()+3
  
  Check “Reject Input” and “Save.”
  
YAGNESH DARJI April 24, 2022 At 2:52 pm

Thank you very much for this Effort.

I want to find the “LOW” Price of a particular stock for a given month.

Tried your “high” formulae in the same manner for getting low but were unable.

=index(sortn(GoogleFinance($A$1,"high",B118, C118,"DAILY"),2,1,2,0),2,2)

$A$1 = STOCK SYMBOL
B118 = 3/2/2022
C118 = 3/31/2022

- Prashanth April 25, 2022 At 9:16 am
  
  Hi, Yagnesh Darji,
  
  I assume you were trying to find the MIN of the above historical data, not the low price for the specified date(s).
  
  If so, try this.
  
  =index(sortn(GoogleFinance($A$1,"high",B118, C118,"DAILY"),1,1,2,1),0,2)
  
Koleen April 21, 2022 At 9:14 pm

Thank you for this tutorial! I may have missed where I can auto-populate due dates from the event date. For example,

The event date is April 25, 2022, and is located in cell D1.

I have 25 tasks to complete before April 25 – these tasks are due in various increments – 30 days out; 25 days out; 5 days out… etc. and are listed in D4 through D21. Some will be + for tasks after the event.

I need to auto-populate those due dates. What would my formula be?

Thank you!

- Prashanth April 23, 2022 At 9:56 am
  
  Hi, Koleen,
  
  It requires a sample sheet for me to answer. Please feel free to share one in the comment reply below.
  
GJ April 16, 2022 At 5:13 pm

Hi. Thank you for this. How would I formulate the start of a pay period and the end of a pay period given the pay date?

Let’s say the pay date is Friday, Jan 14th, 2022.

I want my sheet to auto-populate the pay period start date (Saturday, Jan 1st, 2022) and the pay period end date (Friday, Jan 7th, 2022) by itself.

I want my sheet to recognize the start date as Saturday and the end date as Friday.

- Prashanth April 17, 2022 At 3:32 pm
  
  Hi, GJ,
  
  Let’s assume the pay date is in D4.
  
  To get the payment period, use the below formulas.
  
  Start: =(D4-weekday(D4)+6)-13
  
  End: =(D4-weekday(D4)+6)-7
  
  If the above doesn’t help, please share a few more examples in your reply.
  
Sam Stern April 1, 2022 At 6:29 pm

You’re a wizard. Here’s my problem. I would be so appreciative.

Looking to add to dates (e.g., =A1+7), but if the end date is a weekend or holiday, push it to the next workday.

My difficulty is that WORKDAY and adding holidays to it will not count them *within* the seven days, but I want to include them, but not if it’s the 7th day itself.

Assume I can array holidays and if needed, weekends.

Thank you in advance!

- Prashanth April 3, 2022 At 2:48 pm
  
  Hi, Sam Stern,
  
  Just enter the holidays (not weekends) in B1:B.
  
  Then try the below formula in C1.
  
  =workday(A1+7-1,1,B1:B)
  
Lexi Maitland January 27, 2022 At 6:49 am

Hi! This was so helpful.

I’m looking to measure Projected Date vs. Actual Date.

B1 (Projected Date) = 1/26/22
C1 (Actual Date) = 1/27/22

The percentage is 100% if the dates are the same.

If the dates are not the same, return the percentage.

Thank you for your time and assistance!!

- Prashanth January 28, 2022 At 11:44 am
  
  Hi, Lexi Maitland,
  
  This may possible if you have a start date.
  
  E.g.:
  A2 (Start Date)
  B2 (Projected Completion Date) = 1/26/22
  C2 (Actual Completion Date) = 1/27/22
  D2 (Duration in Percentage)
  
  =to_percent(days(C2,A2)/days(B2,A2))
  
  It will return 100% if the dates are the same.
  
George January 5, 2022 At 3:26 pm

Hello, I am trying to make a formula that allows me to select a date from the sheets’ calendar but can’t do it. (I want to be able to click on a shell and then get a calendar to select the date)

Thank you in advance, and have a great 2022!

- Prashanth January 6, 2022 At 12:49 pm
  
  Hi, George,
  
  Please follow this – How to Get Date Picker in Blank Cell in Google Sheets.
  
  Thanks.
  
Proserpine December 3, 2021 At 10:37 am

Hi, I love this tutorial. I was able to use some of the functions.

I just have questions on how I can compute if on cell A1 I have a specific date, and on cell B1, I have 6 (represents six months).

How can I have A1 +B1 = C1?

Like A1 (1-19-2021) + B1 (6 months) = C1 (7-19-2021)

I hope you can help me. Thank you.

- Prashanth December 3, 2021 At 10:52 am
  
  Hi, Proserpine,
  
  You can use the EDATE function as below in cell C1.
  
  =edate(A1,B1)
  
  Assume A1 has a valid date and B1 has the number 6.
  
Nitin Jain September 20, 2021 At 11:26 pm

Hi Prashanth,

Thank you for helping out with Google Sheets.

Can you please assist me in Validating the same case for the Google Form?

- Prashanth September 21, 2021 At 9:02 am
  
  Hi, Nitin Jain,
  
  At present, Google Form doesn’t support it. There might be add-ons available for the same.
  
Nitin Jain September 19, 2021 At 12:14 am

Hi,

I am looking for a formula to validate whether the given DOB falls in 18 years to 45 years or not?

It should be through validation, and if he doesn’t fall in the 18 to 45 years range, show validation help text “You are not eligible.”

Thanks

- Prashanth September 19, 2021 At 7:07 am
  
  Hi, Nitin Jain,
  
  Assume the cell in question is B2.
  
  Go to DATA > Data Validation. Choose the Criteria “Custom formula is” and enter the below rule in the given field there.
  
  =and( isdate(B2), gte(datedif(B2,today(),"Y"),18), lte(datedif(B2,today(),"Y"),45) )
  
  You can learn the use of the GTE and LTE functions here – Comparison Operators in Google Sheets and Equivalent Functions.
  
  Note:- To apply the rule to a range like B2:B100, just change the “Cell range” accordingly within the Data Validation dialogue box. The formula will be the same.
  
Brian M July 8, 2021 At 10:47 pm

Hello,

The tutorial was great. I used a few formulas already.

I am trying to find a way to calculate how many projects are past their renewal dates. So I have column A with dates, and I want to calculate how many projects are past today’s date, automatically without changing the date each time. Is there a way to calculate a total?

- Prashanth July 9, 2021 At 9:18 am
  
  Hi, Brian M,
  
  Use the Countif() with today() as below.
  
  =countif(A1:A,"<"&today())
  
Steven Umpleby June 17, 2021 At 1:58 pm

Hi Prashanth,

You helped me out with the formula below. Is it possible to select two weeks from a selected date?

=ArrayFormula(sumifs(D5:D,B5:B,B2,month(C5:C),C2))

Cheers
Steve

- Prashanth June 17, 2021 At 4:17 pm
  
  Hi, Steven Umpleby,
  
  You can try this formula.
  
  =ArrayFormula(sumifs(D5:D,B5:B,B2,C5:C,">="&C2,C5:C,"<="&C2+14))
  
  Note:- Please replace the earlier formula (month number) with the below one.
  
  =ArrayFormula(sumifs(D5:D,B5:B,B2,datevalue(C5:C),">0",month(C5:C),C2))
  
Andrew Dahler March 19, 2021 At 11:23 pm

Trying to calculate how many days it takes us to complete our job process.

We currently add a new job every day.

I am using NETWORKDAYS – Column A contains our start date and Column B contains our finish date.

This works great as long as there is a date entered into the “finish date” field.

If we have not completed this task, it shows up as a -31580.

These jobs are not completed in numerical order.

Is there a way to have the NETWORKDAYS formula ignore blank fields in the hopes of alleviating the negative numbers as well as the need to update the formula in that field, every time a process is completed?

Any help?

- Prashanth March 21, 2021 At 12:08 pm
  
  Hi, Andrew Dahler,
  
  This formula may help?
  
  =if(iferror(datevalue(A2)*datevalue(B2))>0,networkdays(A2,B2),0)
  
  The formula would output 0 in such cases. If you want blank, just remove the 0 that you can find in the last part of the formula.
  
- Prashanth March 28, 2021 At 4:03 pm
  
  Hi, Andrew Dahler,
  
  If you want examples, please follow this tutorial.
  
  Calculate Number of Days Ignoring Blank Cells In Google Sheets – Array and Non-Array Formulas
  
kris hart January 29, 2021 At 1:19 am

Hoping you can help me.

I’ve read through your entire article and my head is spinning.

We own a storage yard for containers to be stored. We have to charge customers from the day they begin to store a container here to the day it leaves our yard.

When I enter the start date and the end date and use the =DAYS or =DATEDIF, OR =MINUS(A2,B2) the result is not including the day they begin.

For example, 1/1/2021 – 1/7/2021 is calculating 6 days when we would bill 7 days.

- Prashanth January 29, 2021 At 10:05 am
  
  Hi, kris hart,
  
  The date difference between 01/01/2021 and 01/01/2021 will be 0.
  
  So, if A1 contains 01/01/2021 and B1 contains 02/01/2021 (both in DD/MM/YYYY format as per my sheets’ formatting), the following formula will return 1.
  
  =days(B1,A1)
  
  To make both the dates inclusive, modify the formula as below.
  
  =days(B1,A1)+1
  
Carmen January 26, 2021 At 11:21 pm

Hi, I need help with Google Sheets formatting of dates.

If I have two columns:
Column E – to be filled with dates
Column F – 90 days from date in Column E

How can I get Column F to be filled in automatically when I key in dates in Column E?

- Prashanth January 27, 2021 At 4:02 pm
  
  Hi, Carmen,
  
  If you start entering the dates from E2 (in E2:E as cell E1 may contain a label), then you can try the below array formula.
  
  =ArrayFormula(TO_DATE(IFERROR(if(DATEVALUE(E2:E),E2:E+90,))))
  
  Empty column F and insert the formula in cell F2.
  
Paul wurz December 25, 2020 At 12:21 am

Hi, Can you help me find a formula for my Google Sheets?

Example:
A1: 160000
A2: 15000
A3: =(date)
A4: =sum(A1-A2) starting from cell A3

I need to subtract cell A2 from cell A1 every 30 days into cell A4.

- Prashanth December 25, 2020 At 10:28 am
  
  Hi, Paul wurz,
  
  This may require an Apps Script which I’m not familiar with.
  
  Please try asking the corresponding community here – https://developers.google.com/apps-script/community
  
Rps December 22, 2020 At 12:57 am

The week from Friday to Thursday, what is formula last week and update automatically.

- Prashanth December 22, 2020 At 11:14 am
  
  Hi, Rps,
  
  Dates in A2:A.
  
  To filter the last week’s dates (Friday-Thursday) from this date range, use the below formula.
  
  =filter(A2:A,weeknum(A2:A,15)=weeknum(today(),15)-1)
  
Kayi December 12, 2020 At 3:55 am

Hi,

I have a column from Google Sheets with a date on it. Column B.

I need Column A to calculate the date to show the Monday of that week.

I have this formula that works, B2-WEEKDAY(B2,3), but I need to make it an array so it auto-fills. How can I do this?

- Prashanth December 12, 2020 At 8:31 am
  
  Hi, Kayi,
  
  Formula:
  
  =ArrayFormula(to_date(if(len(B2:B),B2:B-WEEKDAY(B2:B,3),)))
  
  Make column A empty, then insert this formula in A2.
  
Hardest Easy November 18, 2020 At 8:19 am

Hello,

I am trying to display the $ value present in a cell associated with the last day of each month given a daily $ value is collected in a spreadsheet each day. How can you only collect that last day of each month (12 values) among 365 values in a year?

- Prashanth November 19, 2020 At 8:25 am
  
  Hi, Hardest Easy,
  
  Assume the said two columns are A2:A (dates) and B2:B (dollar values). The cells A1 and B1 contain the labels.
  
  If so, try this formula.
  
  =array_constrain(sort(sortn(sort({A2:B,month(A2:A)},1,0),9^9,2,3,0)),9^9,2)
  
  The formula must be adjusted as per your LOCALE settings as detailed here – How to Change a Non-Regional Google Sheets Formula.
  
  If you want the formula explanation, please mention that in your reply.
  
Bijon SARKAR June 13, 2020 At 8:05 am

I’ll try to explain my problem, please help.

Id of Crew|Name|In|Out|D.Hrs|Night D.Hrs
N10001|AAA|05-06-2020 16:00|05-06-2020 23:00|07:00|01:00 (btn 22 to 06)
N10002|BBB|06-06-2020 23:00|07-06-2020 08:00|09:00|08:00 (btn 22 to 06)
N10006|XYX|08-06-2020 03:00|09-06-2020 23:30|20:30|03:00 (btn 22 to 06)
N10010|LMN|08-06-2020 21:00|08-06-2020 23:45|02:45|01:45 (btn 22 to 06)

- G T August 26, 2020 At 2:20 pm
  
  Hi Bijon,
  
  I think your formula will be this.
  
  Hours = 24 * (Out – In)
  
  You don’t even need a formula.
  
  Geoff
  
Tracy DeCrosta June 12, 2020 At 2:22 am

I would like to turn a cell red if it is five years from the date listed in the cell. Is this possible with google sheets?

- Prashanth June 12, 2020 At 10:29 am
  
  Can you explain a little more, please?
  
  The date in which cell and which cell do you want to turn to read on which date.
  
Bijon SARKAR June 11, 2020 At 10:41 pm

Hello,

Could anybody help me?

I want calculating night duty Hrs between two date-time. i.e.,

One who joining his duty at dd/mm/yyyy 10:00:00 hrs and out from duty at dd/mm/yyyy 04:45:00 hrs.

I want to calculate how many Hrs having worked between 22:00 to 06:00 between joining and outing his duty. (excel sheet or Google Sheet only).

Jon January 27, 2020 At 6:22 am

I’m trying to keep up with my monthly dues, and on my Google Sheet, I want the whole row to be highlighted if the due date is today.

I know how to highlight the whole row, but I don’t know how to automatically change the date on the cell.

For example, I would initially enter January 26 in the cell. After January 26, I want it to change to February 26 so that in conditional formatting, I can highlight any row with today’s date (every 26th).

- Prashanth January 27, 2020 At 9:36 am
  
  Hi, Jon,
  
  To know how to advance dates you can check this guide.
  
  Get a Dynamic Date that Advances/Resets in Google Sheets
  
  But I think the following formula will work in your case.
  
  If the said date is in cell B5, to highlight entire row # 5 on every 26th, select row # 5, and use this =day(today())=26 formula in conditional formatting.
  
Terry Heath September 12, 2019 At 6:07 pm

Worked it out…

=sumproduct(text(Data!A:A, "mm/yyyy")=text(today(), "mm/yyyy"), Data!E:E)

This works!

Terry Heath September 12, 2019 At 3:01 pm

Hello,

Could you help me?

I have a dashboard that shows the running totals of sales for the day, week and month.

I have managed to program formula for day & week but I am struggling with calculating sales in the current month.

My data is set out with a transaction date in column A and transaction total in column I.

This set of data is continually updated through an automated workflow integration.

To calculate weekly total I use this formula: sum(filter(Zapier!I:I,weeknum(Zapier!A:A,2)=weeknum(today(),2)))

Is there a formula that would do the same for a monthly total?

Thank you in advance for your assistance

Erin August 28, 2019 At 7:03 pm

Hi Prashanth,

I thought your response to Anakowi on June 16, 2019, might provide the answer for which I’ve been searching!

Unfortunately, though, when I plugged it into my Google Sheets document, it did not work as I’d hoped. I’ll explain what I need and hopefully, you can advise me accordingly! Thanks, so much, in advance!!

So, I’ve created a file to include all of the coupons and sales ads, including item type, brand, store, sale amount, and each coupon’s date of expiration.

What I want to have happened is… whenever the expiration date is TODAY, the cell is (let’s say) RED. When it is within the next 7 days, I want the cell to be GREEN. If the expiration date is 7-14 days from today, I want it to be YELLOW.

I’ve now tried your suggestion to the most recent poster, as well as another blogger’s suggestion, which is as follows:

Format Rules –> Format Cells if — >
Custom Formula IS –>

=if(WEEKNUM((INDIRECT("B"&ROW())))=WEEKNUM((TODAY()+7), 1), 1,0)=1

Formatting Style –>

YELLOW (Fill Bucket color selection)

However, neither option has worked… am I missing something or what do you suggest?

Thanks in advance!

- Prashanth August 29, 2019 At 9:55 am
  Hi, Erin,
  
  I have a similar tutorial here – How to Highlight Cells Based on Expiry Date in Google Sheets.
  I have my expiry dates in B2:B. Here are the conditions and custom formulas to highlight B2:B based on expiry.
  
  Condition 1: Highlight the cell to Red, if the expiration date is Today.
  Formula Rule 1: =B2=today()
  Note: If you want to highlight multiple columns, I mean B2:Z instead of B2:B, use it as =$B2=today().
  
  See this guide for detailed info – Highlight an Entire Row in Conditional Formatting in Google Sheets.
  
  Condition 2: Expiry date is within the next 7 days, I want the cell to be in Green.
  Formula Rule 2: =and(B2>today(),B2<=today()+7)
  
  Condition 3: If the expiration date is 7-14 days from today, I want to highlight the cell to Yellow.
  Formula Rule 3: =and(B2>today()+7,B2<=today()+14)
Anakowi June 15, 2019 At 7:59 pm

Thank you, Prashanth. This info has been so useful.

I’m now stuck trying to automate the display of a date based on TODAY’s date to use with other calculations. I want the display date to always be “Friday” (start_date) and only changes when the next Friday is reached.

For example, today is Sat, 15 June 2019. So the display date will be Fri, 14 June 2019 until the next Friday arrives, i.e., Fri, 21 June 2019. Is it possible?

I’ve tried using WEEKNUM as the closest method to set Friday as the first day of the week, but it’s not giving me the result I need.

- Prashanth June 16, 2019 At 9:48 am
  
  Hi, Anakowi,
  
  That’s possible and detailed here – Get a Dynamic Date that Advances/Resets in Google Sheets.
  
  Here is the formula for your case.
  
  =TODAY()-MOD(TODAY()-date(2019,6,14),7)
  
  You can replace date(2019,6,14) with any cell reference. Further, the output may be in DATEVALUE. If so, you may need to format the output cell to date from Format > Number > Date.
  
  Best,
  
  Prashanth KV
  
Danny Alva January 24, 2019 At 2:00 am

I’m trying to set my utility bills schedule and the dates to show the upcoming payment date. So If the BILLING DATE is past the CURRENT DATE it should change the MONTH to the next.

Any help would be appreciated,

- Prashanth January 24, 2019 At 8:06 am
  
  Hi, Danny Alva,
  
  Assume your billing date, upcoming payment date, is in cell B1. If so, in cell C1, you can try this formula.
  
  Cheers!
  
- Prashanth February 10, 2019 At 9:15 am
  
  Hi, Danny Alva.
  
  I have just found that the formula was missing in my earlier reply due to some technical issue. So I have pasted the formula as an image this time. Please check.
  
  Thanks.
  
Vijay November 17, 2018 At 2:24 am

Thanks for your extremely useful article. Could I get your help in getting a couple of dates as below?

1. The date of the Friday last week

2. The date of the Friday Last month.

Much Appreciated.

Vijay

- Prashanth November 17, 2018 At 8:50 am
  
  Hi, Vijay,
  
  To find the date of the last Friday, you can try;
  
  =TODAY()-WEEKDAY(TODAY())-1
  
  This is based on the Weekstart Date and End Date calculation.
  
  But I am unsure about your second question.
  
  You may try this.
  
  =edate(today(),-1)-WEEKDAY(edate(today(),-1))-1
  
Seth June 20, 2018 At 12:53 am

Hi, thanks for this. Very useful. Any clue how to enter a function that will always display the date of a particular upcoming day? For example, I want a cell to display the date of next Tuesday up to and including that Tuesday. On the Wednesday after that Tuesday I want it to display the following Tuesday’s date.

- Prashanth June 20, 2018 At 9:42 am
  
  Hi, Seth,
  
  Welcome to InfoInspired!
  
  You can try the below formula. Replace the cell reference G1 with weekday numbers. 1 represents Sunday, 2 for Monday and so on. If you put 1 in G1 or replace G1 with 1, the formula would populate the dates from today to coming Sunday.
  
  =ArrayFormula(if(weekday(today())>G1,row(indirect("A"&TODAY()&":"&today()-weekday(today())+7+G1)),row(indirect("A"&TODAY()&":"&today()-weekday(today())+G1))))
  
  Cheers!