This tutorial about utilising Google Sheets date functions divided into three sub titles for easy explanation – Simple Date Functions, Standard Date Functions and Advanced Date Functions.
I have spent lots of my time writing tutorials and eBooks related to Google Sheets’ advanced functions, commands and other important aspects related to Google Sheets. As a result, you can see plenty of tutorials related to Google Sheets on this Site. So what is the point?
The thing is that I intentionally opt out of writing some of the commonly using Google Sheets functions which I thought users might know. The Google Sheets Date Functions are one among them. In order to make Info Inspired a complete reference source of Google Sheets, I am filling the void space with the so called ‘missing’ functions or commands. So here we are. Let us begin with how to utilise Google Sheets Date functions.
Simple Google Sheets Date Functions
1. Google Sheets TODAY Function
How to use TODAY function in Google Sheets?
When you want to return current date on a cell use this function. Remember, the date thus inserted will auto update.
2. Google Sheets DAY Function
How to use DAY function in Google Sheets?
The DAY function in Google Sheets returns the day from a given date.
- In Cell E2, the formula result will be the current day.
- Similarly in Cell E3, the formula result will be the day from the given date reference in B3.
- In Cell E4, the formula result will be the day from a given date inside the formula.
3. Google Sheets NOW Function
How to use NOW function in Google Sheets?
NOW Google Sheets function is similar to TODAY function. But it returns both current date and time which is also auto auto volatile.
4. Google Sheets MONTH Function
How to use MONTH function in Google Sheets?
Google Sheets MONTH function returns the month from a specific date.
5. Google Sheets YEAR Function
How to use YEAR function in Google Sheets?
Google Sheets YEAR function returns the year from a specific date.
Standard Google Sheets Date Functions
As I told you the above categorisation of Google Sheets Date Functions are simply for explanation purpose. There is no such categorisation in real sense. So let us begin with Standard Google Sheets Date functions.
1. Google Sheets DATE Function
How to use DATE function in Google Sheets?
=DATE(year, month, day) Function
You can utilise this Google Sheets date function to convert a provided year, month, and day into a date. Some times we keep year, month and date in separate columns. So this function is to return date from such entry.
2. Google Sheets DATEVALUE Function
How to use Google Sheets DATEVALUE function?
This is a very useful date function in Google Sheets. It converts a date stored as text to a serial number that Google Sheets recognise as date.
When you use string inside the formula you can apply different date formats. See the below example where I applied different date formats inside the formula. But when you use DATEVALUE function to refer to a cell, that date string should be entered as per the standard date format set on your spreadsheet.
You can check this by entering a date in any cell. If it’s a date, it will normally align to right if no formatting rule is set else it will align to left. Same format in text can be used with the function. See cell G10 in the below example.
You can later utilise to_date() function to convert back the serial number to date.
3. Google Sheets DAYS Function
How to use DAYS function in Google Sheets?
Utilise this google sheets date function to find the date difference in number. That means the number of days between two given dates.
Google Sheets Advanced Date Functions
Now let us see how to utilise some advanced date functions in Google Sheets. These functions are also simple but as a beginner some of you may find a tough time with it. That is why I put it under advanced date functions. Here we goes!
1. Google Sheets EDATE Function
How to use EDATE function in Google Sheets?
Use Google Sheets EDATE date function to return a date after or before a given date that based on number of months as input. For example you can get a date after six months of a given date or 5 months back of a given date. That months part is important. See the formulas below.
This function accepts date value, see DATEVALUE function, also which is already detailed above.
2. Google Sheets ISOWEEKNUM Function
How to use ISOWEEKNUM function in Google Sheets?
To understand this Google Sheets function, you should first know what is ISO Week. This Wiki article will be helpful to you to know about ISO Week in detail. But here it is in a nutshell for our function purpose.
- The function follows the ISO 8601 date and time standard.
- In this standard weeks begin on Monday and end on Sunday.
- As per this standard Week 1 of the year is the week that containing the first Thursday of the year. See the below image.
Now to our function. The above ISOWEEKNUM function returns the number of the ISO week of the year where the provided date falls. See the below examples.
3. Google Sheets NETWORKDAYS Function
How to use NETWORKDAYS function in Google Sheets?
=NETWORKDAYS(start_date, end_date, [holidays])
You can utilise NETWORKDAYS function to get the number of net working days between two given dates. Then what is networking days?
Normally networking days are the total number of days excluding weekends. Weekends here means Saturday and Sunday. If you want to change the weekends there is another function, that follows after this.
Optionally you can exclude holidays also. Examples are always the best method to learn. Important: Holidays should be entered as date value. Check the above DATEVALUE function to convert holidays to date value.
To avoid confusion, as you can see on the examples, I only used cell reference in the function. You can use string method too. To cut short this tutorial as well as to make things simple, I’ve opted out the string method this time. The same will be followed in the coming examples.
4. Google Sheets NETWORKDAYS.INTL Function
This is one of my favourite Google Sheets Date Functions. You will also agree to it once you learn it.
How to use NETWORKDAYS.INTL function in Google sheets?
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
This function is superior than NETWORKDAYS. How?
NETWORKDAYS.INTL is same as NETWORKDAYS function. The only difference is the former function allows you to specify the weekends. This is useful when in some countries like middle east, Friday and Saturday are considered as weekends. To use NETWORKDAYS.INTL function, you should know the weekend numbers.
What are the Weekend Numbers in Google Sheets?
- Saturday, Sunday – Weekend Number is 1
- Sunday, Monday – Weekend Number is 2
- Monday, Tuesday – Weekend Number is 3
- Tuesday, Wednesday – Weekend Number is 4
- Wednesday, Thursday – Weekend Number is 5
- Thursday, Friday – Weekend Number is 6
- Friday, Saturday – Weekend Number is 7
- Sunday only – Weekend Number is 11
- Monday only – Weekend Number is 12
- Tuesday only – Weekend Number is 13
- Wednesday only – Weekend Number is 14
- Thursday only – Weekend Number is 15
- Friday only – Weekend Number is 16
- Saturday only – Weekend Number is 17
Now let us go through few examples.
Here in this function also holidays should be used as date value. Check DATEVALUE function above.
To understand the above NETWORKDAYS.INTL formula in the last row, see the below calendar.
We considered date starting from 20/12/2017 to 31/12/2017 in the formula. In this again we specified the holidays as 25/12/2017 and 26/12/2017 using respective date values 43094 and 43095. We used the weekend number 7 in the formula, which denotes weekends as Friday and Saturday. So the remaining days I’ve marked in the above calendar and when you count it, you will get number 6 and that is the result of the formula.
5. Google Sheets WORKDAY Function
How to use WORKDAY function in Google Sheets?
=WORKDAY(start_date, num_days, [holidays])
WORKDAY function has lots of similarities with NETWORKDAYS function which we already learned above.
We utilise NETWORKDAY to find number of working days between two given dates excluding weekends and holidays.
Here in WORKDAY, we can find a date after a given number of working days. Suppose today is 1/1/2017. We can use WORKDAY function to find a date after 90 working days. Working days means days excluding weekends and holidays.
First please learn above NETWORKDAYS function then come back to WORKDAY function so that you can easily grasp.
Helpful Tips: Use negative number in the place of num_days to count backward.
6. Google Sheets WORKDAY.INTL Function
How to use WORKDAY.INTL function in Google Sheets?
=WORKDAY.INTL(start_date, num_days, [weekend], [holidays])
Any Google sheets date function ending with INTL are so special. Such Google Sheets Date Functions target international users. Date format and weekends may differ among countries. So it is special.
This is same as WORKDAY function. But here the difference is, you can decide what is the weekend. Please refer WORKDAY function above first. If you directly jumped to this title I will explain you what is the use of WORKDAY.INTL.
You can use WORKDAY.INTL function to return a date after giving the input as number of working days. Suppose today is 01/01/2017. When you want to find a future date after 180 working days, that also after excluding weekends of your choice and your selected holidays, you can use WORKDAY.INTL function.
There are weekend codes or identification numbers that required in the function. Find it above under the function NETWORKDAYS.INTL.
Here in this example I’ve used weekend number 7 which indicates Friday and Saturday as weekends. If you want to use Saturday and Sunday as weekend days, straightaway use WORKDAY function or use weekend number 1 here in this function.
7. Google Sheets YEARFRAC Function
How to use YEARFRAC function in Google Sheets?
=EARFRAC(start_date, end_date, [day_count_convention])
YEARFRAC is yet another useful Google Sheets date function. Use this date function to return number of years including fractional years between two given dates. You can also specify which day count conversion to be followed. There are four day count conversions in Google Sheets.
What is day count conversion?
It is how many days in a year to be considered. Whether it’s 360 days as a year or 365 days or actual number of days in a year as year.
Day count conversion numbers.
- 0 indicates 30 days each month. That means 360 days in a year, i.e. 30/360
- 1 indicates actual days between two specified days, i.e. actual/actual.
- 2 indicates actual number of days between two specified days, but assumes 360 day year, i.e. actual/360.
- 3 indicates actual number of days between two specified days, but assumes a 365 day year i.e. actual/365.
- 4 is similar to 0, this calculates based on a 30 day month and 360 day year, but adjusts end-of-month dates according to European financial conventions, i.e. European 30/360.
Confused? Don’t worry! I have applied all the above count conversion numbers with the below functions. Also a detailed information about the count conversion numbers you can find on this Wiki page.
Now to the function.
8. Google Sheets DATEDIF Function
How to use DATEDIF function in Google Sheets?
=DATEDIF(start_date, end_date, unit)
DATEDIF is one of the very useful function among the available Google Sheets Date Functions. This is not DATED IF function. It is DATE DIF function. What’s it?
This function will do the job of three functions. What are they? DATEDIF function calculates the number of days, months, or years between two given dates with the help of unit abbreviation.
What is the “unit” in the syntax?
It’s a text abbreviation for unit of time. Where;
- “Y” stands for number of whole years between a start date and end date.
- “M” stands for number of whole months between a start date and end date.
- “D” stands for number of days between a start date and end date.
- “MD” stands for number of days between a start date and end date but after subtracting whole months.
- ‘YM” stands for number of whole months after subtracting whole years.
- “YD” stands for the number of days between start date and end date, assuming start date and end date were no more than one year apart.
Now take a look at the example.
9. Google Sheets WEEKDAY Function
How to use WEEKDAY function in Google Sheets?
WEEKDAY function returns a number representing the day of the week of the provided date.
First I will tell you what is type in WEEKDAY date function. Type is optional in the function. If omitted the day will count from Sunday as 1 and Saturday as 7 or else you can include 1. Both are same. If you include 2 then the counting starts from Monday to the date specified. See few examples.
10. Google Sheets WEEKNUM Function
How to use WEEKNUM function in Google Sheets?
WEEKNUM function returns a number representing the week of the year where the given date falls.
Similar to WEEKDAY function you can determine which date to consider as the week starting day. But there is a twist. Here weeks not day. So refer the below chart to use type.
Now straight to the function.
Let us conclude this ultimate Google Sheets Date Functions tutorial. Now to the last function.
11. Google Sheets EOMONTH Function
How to use EOMONTH function in Google Sheets?
This function simply returns the end of the month of a given date.
Suppose our date to be checked in cell C4 is 01/24/2017.
You can apply the formula as below.
In this formula “0” represents the current month. It will return the last date of the month, i.e., 01/31/2017. If you change the value to “1”, it will return the next month last date and so on.
I don’t want to drag this tutorial to any more! Because I know already this tutorial got bit lengthy. I myself never thought these much time I would take to complete this tutorial. I took more than 10 hours at a stretch to complete this Google Sheets Date Functions tutorial! If you find it useful please share.