Cloud Apps Software

DATEDIF How to Calculate Difference Between 2 Dates

The DATEDIF function calculates the number of periods between two dates. The best way to remember this function is it calculates the DATE DIFference between two dates.

What is DATEDIF?

The DATEDIF function is a popular formula that has 3 parameters which are all required. If you [encounter any error]({{< relref “#datedif-errors-and-problems” >}}) when using this function check you have populated all three fields correctly.

The function reference is as follows:

=DATEDIF(start_date, end_date, unit) 

The start_date parameter should be the earliest date, the end_date being the latest date, and the unit is the value of the periods you want returned from the difference between those two dates.

Accepted values in Google Sheets for the unit value are:

Date Difference in Days

By setting the third parameter to the DATEDIF formula to D it means the resulting value returned will calculate the number of days between the two dates. Here are some examples:

11 June 20201 June 2020=DATEDIF(A1, B1, "D")0
21 June 20202 June 2020=DATEDIF(A2, B2, "D")1
31 June 202031 May 2020=DATEDIF(A3, B3, "D")#NUM!

Several things to note about these tests when using the DATEDIF function and the unit parameter as D:

  1. The dates are not inclusive. Notice the first row is 0, if it were inclusive it would have been 1.
  2. Should the end_date come before the start_date you will get an #NUM! error. Make sure your end_date occurs on or after the start_date.

Date Difference in Months

If you want to calculate the difference between two dates in months, then set the third parameter of the formula to "M", here are some interesting examples of what this looks like for certain values:

129 February 202029 February 2020=DATEDIF(A1, B1, "M")0
229 February 202028 March 2020=DATEDIF(A2, B2, "M")0
329 February 202029 March 2020=DATEDIF(A3, B3, "M")1
429 February 20201 March 2021=DATEDIF(A4, B4, "M")12
531 January 202029 February 2020=DATEDIF(A5, B5, "M")0
631 January 20201 March 2020=DATEDIF(A6, B6, "M")1

As can be seen from the table above, the day of the month needs to be either eclipsed or matched for the returning value to increment. In rows 5 and 6 especially we can see the days in the month of February 2020 would not have eclipsed the start_date of 31st January 2020, but when the end_date advances into the next month (1st March) the increment occurs.

Therefore, if your start_date is the end of a month containing 31 days it will not be until the 1st day of the following month, preceding a month containing only 28, 29 or 30 days, that the value will be incremented.

Date Difference in Years

If you want to calculate the difference between two dates as years then set the third parameter value to "Y". Here is what this would look like based on the following inputs:

129 February 202029 February 2020=DATEDIF(A1, B1, "Y")0
229 February 202028 February 2021=DATEDIF(A2, B2, "Y")0
328 February 202028 February 2021=DATEDIF(A3, B3, "Y")1
429 February 20201 March 2021=DATEDIF(A4, B4, "Y")1

As can be seen from the examples above, it’s not until the date and month of the start_date has been eclipsed or matched the value returned by the DATEDIF formula increments. This is very similar to when calculating the [date difference in months]({{< relref “#date-difference-in-months” >}}).

Date Difference in Days Irrespective of Months

If you need to calculate the difference in days between two dates irrespective of the difference between months then you’ll need to use "MD" as the value of the third parameter, as demonstrated below:

131 January 202031 January 2020=DATEDIF(A1, B1, "MD")0
231 January 202029 February 2020=DATEDIF(A2, B2, "MD")29
331 January 20201 March 2020=DATEDIF(A3, B3, "MD")-1
431 January 20202 March 2020=DATEDIF(A4, B4, "MD")0
531 January 202030 March 2020=DATEDIF(A5, B5, "MD")27
631 January 202031 March 2020=DATEDIF(A6, B6, "MD")0
731 January 20201 April 2020=DATEDIF(A7, B7, "MD")1

As previously noted with the [difference in days]({{< relref “#date-difference-in-days” >}}) we can see from the examples above the difference in days is not inclusive. However, there are some interesting results:

Row 3 shows a result of -1. Why? This implies there was a day short in the previous month of getting to the same date, and the current date of 1st March 2020 is 1 day short of the 31 days (29 days in February + 1 day in March = 30 days – 31 days = -1). This then puts the remaining values of that month (in March) out, until we hit the new month April.

Date Difference in Whole Months

If you want to calculate the difference between two dates in whole months you can set the third parameter to the DATEDIF formula to "YM".

131 January 202031 January 2020=DATEDIF(A1, B1, "YM")0
231 January 202029 February 2020=DATEDIF(A2, B2, "YM")0
331 January 20201 March 2020=DATEDIF(A3, B3, "YM")1
431 January 202031 March 2020=DATEDIF(A4, B4, "YM")2

The results from this result set above are fairly simple to understand and are consistent with what we already know about – the day of the month needs to be matched or eclipsed to increment the value.

Date Difference in Days Irrespective of Years

Similar to our previous example with [date differences in days irrespective of months]({{< relref “#date-differences-in-days-irrespective-of-months” >}}) we have a similar calculation but this time irrespective of years, here are some examples:

129 February 202029 February 2020=DATEDIF(A1, B1, "YD")0
229 February 202028 February 2021=DATEDIF(A2, B2, "YM")365
329 February 20201 March 2020=DATEDIF(A3, B3, "YM")0

Consistent with the days difference irrespective of months we see that where the start date isn’t matched by day of month and month, it calculates the difference the previous month fell short.

Benefits of DATEDIF Over Subtracting Two Dates

An objection to using the DATEDIF formula is that it achieves the same result when subtracting dates, which is certainly far simpler than trying to remember a formula, and it’s parameter values.

The biggest benefit of the DATEDIF formula is that it helps to calculate the difference between two dates by providing us with the type difference we need. Therefore, the DATEDIF formula is more versatile for various use cases.

Another minor benefit to using the DATEDIF function when calculating the difference in days between two dates over the simple subtraction method is if the input dates contain time.

11 June 202011 December 2020=B1-A1193
21 June 202011 December 2020=DATEDIF(A2, B2, "D")193
31 June 2020 8:00:0011 December 2020 10:00:00=B3-A3193.083333
41 June 2020 8:00:0011 December 2020 10:00:00=DATEDIF(A4, B4, "D")193

You can still achieve the same answer as the DATEDIF function, but you would need to remember and apply to use the TRUNC formula to achieve the same answer in row 3 above, like so:

51 June 2020 8:00:0011 December 2020 10:00:00=TRUNC(B5-A5)193

DATEDIF Errors and Problems

There are a couple of things to be mindful of when using the DATEDIF function. As already shown above in the [DATEDIF days examples]({{< relref “#date-difference-in-days” >}}) we had a result on the last row that gave us an error.

If you do get a #NUM! error check the parameter values are correct, and the earliest date is the start_date value, and the later date is the end_date value.

Provided your parameter values have been set correctly, the only other type of error which may cause problems and may not be as obvious is if the locale setting of your dates are not what you anticipated.

Incorrect DATEDIF Locale

If you’re getting an answer which definitely isn’t correct you will want to check the settings of your spreadsheet to ensure it correctly interprets a date field value.

For example, some countries, such as the UK and Australia, have dates set to the following format: DD/MM/YYYY whereas other countries, such as Canada and the Unites States, have date values set to the format of: MM/DD/YYYY.

101/06/202011/12/2020=DATEDIF(A1, B1, "D")193 for DD/MM/YYYY folks, or 311 for MM/DD/YYYY folks!

Ways to minimise this error occurring would be to quickly check your locale settings are correct before starting a new spreadsheet, or when modifying a spreadsheet.

In a Google Sheet this would be simply going to File > Spreadsheet Settings > Locale – change this to your country.

{{< figure src=”/images/datedif-check-locale.png” caption=”Check spreadsheet locale settings” alt=”Google Sheets locale setting” >}}

Another way to check is to input a date that wouldn’t be a date in the other format, for example, try 01/13/2020 and 13/01/2020 – one of these will not work and your DATEDIF formula will return a #VALUE! error as the date fields entered aren’t actual dates.


In this article we’ve done a deep dive into the DATEDIF formula and seen the results it provided based on different parameter values.

We’ve also seen how the formula can be better suited than simply subtracting two dates, and we’ve diagnosed our problems and fixed them.

Overall the DATEDIF function is a fantastic function to use, and you should look at incorporating it more in your own daily use. If you’d like to learn more about the function in Google Sheets, you can read the [documentation]({{% relref “datedif-docs” %}}).

Cloud Apps

3 Simple Steps To Send Email With Amazon SES

If you have a custom domain and want to be able to send email through your personal Gmail account without having to register for a Business Gmail account then here’s how you can do it using Amazon’s SES.


If you haven’t done so already you will need to set up your custom domain in Amazon’s SES. Here is a detailed step-by-step guide on how to create your email accounts using Amazon SES, Lambda and S3.

Once you’ve completed the steps on setting up your custom email address you can proceed with setting up the ability to send email from Gmail.


The steps in this guide to send email from your custom domain within your personal Gmail account will be as follows:

  1. Create an SMTP account with Amazon’s SES.
  2. Register your SMTP credentials in Gmail.

Let’s go through each of these steps in detail.

Step 1 – Create SMTP Account in Amazon SES

You will need to login to your AWS Amazon account and then from the Services menu click on Simple Email Service.

From the SES page on the left sidebar click on the label SMTP Settings.

Here you will see some core configuration settings for your SMTP credentials which you will need so you might want to copy them to your text editor:

  • Server Name:
  • Port: 25, 465 or 587
  • Use Transport Layer Security (TLS): Yes
  • Authentication: Your SMTP credentials.

Your configuration for the Server Name may be slightly different if you have selected a different location (look up at the top right-hand corner next to Support – to see which region your domain has been registered).

Register SMTP Credentials

To finish off this process click on the Create My SMTP Credentials button and follow through each of the prompts.

Create an IAM User Name to distinguish this user’s credentials. Once you have a basic user name then click on Create.

This next screen is very important as it will contain the unique user name and password that Gmail will need to create your SMTP account. Your username will be a combination of capitalised letters and numbers, and the password will be somewhat the same but with lower case letters.

Once you have captured these details and stored them safely it’s time to head on over to Gmail to register our new sending email address.

Step 2 – Register SMTP E-Mail in Gmail

Log in to your personal Gmail account, eg., and once you’re in click on the settings icon (usually an icon in the form of a bolt) followed by the Settings drop down menu item.

From the Settings area click on the tab with the label Accounts and Import, then find the area labelled Send mail as and within this area click on the link Add another email address.

This should pop up a modal window where you need to enter the following details:

  • Name: Your Name
  • Email address:
  • Untick “Treat as alias” as you capture email through Lambda & S3 if you’ve followed the [prerequisite]({{< relref “#prerequisite” >}}) above.

On the last window enter the following details you’ve captured from Amazon SES:

  • SMTP Server: (the Server Name)
  • Port: 587
  • Username: SMTP credentials username
  • Password: SMTP credentials password
  • Select Secured connection using TLS (recommended)

Finally, click on the Add Account button and then Google will send through an email you will need to open and click on a link to verify.

If you’ve set up your Lambda and S3 inbound email service to forward through any emails to this very same Gmail service you’ve logged in as you will get an email very shortly. Otherwise, go and fetch the email to where you’ve set the custom domain to forward emails through to and click on the link.

Once you’ve confirmed the link you’re ready to send emails from within your personal Gmail service! All you need to do to send an email using your custom domain is in the From field of your email, to click on the tiny down arrow alongside your email address and then click the domain you wish to send your email from.

Easy as.


In this article we have learned how to set up an Amazon SES SMTP service by creating a unique username and password. You’ve been able to use the configuration settings on Amazon SES to then configure sending emails from your custom domain by using these settings in their respective fields in Gmail’s Settings area.

If you use software to manage your email, you can copy the same process over and apply the same settings.