[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Calculating the differences between two dates in Google Sheets<\/h3>\n
Google Sheets offers several options in which you can calculate your dates. Each varies in the level of complexity to suit your spreadsheet experience or objectives. Let\u2019s first look at the simpler ways you can calculate the difference between dates in Google Sheets.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
How to subtract dates in Google Sheets easily<\/h3>\nOption 1: Simply subtract<\/h4>\n
Firstly, it\u2019s important to note that Google Sheets will process dates exactly as any other numerical value. In other words, you can use simple calculations like add<\/b> and subtract<\/b> to calculate the difference between your two dates. So, the simplest way is to type a simple minus (-) between your dates.<\/p>\n
Simply input: =[end_date]-[start_date]<\/b>[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=[end_date]-[start_date]<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/09\/Date-Dif-GS.png” alt=”google sheets between two dates 0″ title_text=”Date Dif GS” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Please note that when you subtract dates, Google Sheets does not include the start date in the total. For example, let\u2019s say your project starts on 5 May and ends on 12 May. Using a subtract calculation, the result is 7. It counts 7 days: 6th, 7th, 8th, 9th, 10th, 11th, and 12th May.<\/p>\n
So, if you want to include the start date, you must add 1<\/b> to the end of your formula:<\/p>\n
=[end_date]-[start_date]+1<\/b>[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=[end_date]-[start_date]+1[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
You can also use the MINUS<\/a> function in the same way. Simply substitute your minus symbol (–<\/b>) with the word.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =MINUS(<\/b>[end_date]<\/span>,<\/b>[start_date]<\/span>)<\/b><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/09\/Date-Dif-GS-\u2013-1.png” alt=”google sheets between two dates 1″ title_text=”Date Dif GS \u2013 1″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The DAYS function<\/a> is a slightly smarter function you can use to subtract dates in Google Sheets. Not only does it return the total number of days between dates, but also factors in leap years. Here is the DAYS function formula:[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =DAYS(<\/b>[end_date]<\/span>,<\/b>[start_date]<\/span>)<\/b><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/09\/Date-Dif-GS-\u2013-2.png” alt=”google sheets between two dates 2″ title_text=”Date Dif GS \u2013 2″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]Work in a finance role and need to calculate financial interest and fixed-income securities based on a 360-day year? Read our article on How to use the specialist function DAYS360<\/a>.[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The above options were the more basic ways you could calculate between days. The following options are more advanced, however, they leave you with more flexibility in your calculations.<\/p>\n In our example, the projects start and end on specific dates. But, team members will only work on the project on their work days, i.e. weekdays. Let\u2019s take a look at these more advanced options to help us account for these variables.<\/p>\n NETWORKDAYS is a very handy formula to subtract dates within Google Sheets whilst only counting weekdays. This formula stands for Net Working Days, thus excluding the weekends from its calculations.<\/p>\n Moreover, you can customize NETWORKDAYS to calculate non-standard working weeks (i.e. not Monday through Friday) or even to exclude public holidays. If you would like to learn more about this, check out our Guide to using the NETWORKDAYS function<\/a>.<\/p>\n For this example, we will use the most simple syntax for NETWORKDAYS:[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =NETWORKDAYS(<\/b>[start_date],[end_date]<\/span>)<\/b><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]For more customizable formulas, take a look at the NETWORKDAYS syntax<\/a>.[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/09\/Date-Dif-GS-\u2013-3.png” alt=”google sheets between two dates 3″ title_text=”Date Dif GS \u2013 3″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n NETWORKDAYS can successfully calculate the total number of working days<\/b>. But what about if we want to calculate a date difference in months or years? The next option is the perfect solution.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n DATEDIF<\/a> stands for Date Difference. It allows you to calculate the difference in dates in either days, months or years.<\/p>\n Here is the basic DATEDIF syntax:[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=DATEDIF([start_date],[end_date], unit)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The unit<\/b> is the time measurement you want to use to calculate your date difference. Here is a comprehensive list of each unit and what it does:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/09\/google-sheets-between-dates-1.png” alt=”google sheets between dates DATEDIF units” title_text=”google sheets between dates 1″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Here are the examples using the three more basic DATEDIF formulas:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/09\/Date-Dif-GS-\u2013-5.png” alt=”google sheets between two dates 5″ title_text=”Date Dif GS \u2013 5″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]As you can see, we have calculated the difference in dates using days, months and years as the measurement. The days formula essentially works the same way as the MINUS formula. Nevertheless, DATEDIF<\/a> is the only formula that can achieve these calculations using months and years as the measurement units.<\/p>\n Now let\u2019s look at the other DATEDIF<\/a> formulas that you can use in Google Sheets.[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Observing the unit list above, MD, YM and YD find a difference between dates by calculating one unit and excluding the other. Let\u2019s look at some examples and see how each formula works.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/09\/Date-Dif-GS-\u2013-6.png” alt=”google sheets between two dates 6″ title_text=”Date Dif GS \u2013 6″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]As you can see, using the \u201cMD\u201d unit, the formula excludes the number of months in the calculation. Instead, it focuses on the difference between the two days (5th and 16th). Subtracting these from one another comes to 11.<\/p>\n For the \u201cYM\u201d unit, the formula excludes the difference in years between the two dates. It calculates the difference between March and July, which is 4.<\/p>\n Finally, calculating the date difference with \u201cYD\u201d, the calculation excludes the difference in years. It calculates the difference between 3 January and 24 July instead, which gives us 202.<\/p>\n If you would like a more in-depth breakdown of this formula, take a look at our page on DATEDIF syntax<\/a>.[\/et_pb_text][et_pb_text admin_label=”Connections T – Automate between spreadsheets – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button-light vertical-banner-container” _builder_version=”4.27.4″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” saved_tabs=”all” global_colors_info=”{}” global_module=”255443″ theme_builder_area=”post_content”]<\/p>\n Automate data transfers between spreadsheets Find out how<\/a><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As you can see, there are numerous ways to calculate the difference between two dates in Google Sheets. Each formula offers a unique feature to help fulfill your objectives.<\/p>\n If you would like to learn more about special functions in Google Sheets, read the article about the NETWORKDAYS formula in Google Sheets.<\/a><\/p>\n Alternatively, check out related blog posts below!<\/span>[\/et_pb_text][et_pb_code _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]Option 2: The DAYS formula<\/h4>\n
More advanced day-counting functions in Google Sheets<\/h3>\n
Option 3: Use the NETWORKDAYS function<\/h4>\n
Option 4: Use the DATEDIF function<\/h4>\n
How to use MD, YM and YD units in DATEDIF<\/h5>\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/run-automatically-connect-sheet-icons.webp\"){kind=icon}
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\"){kind=icon}
<\/p>\n
<\/span><\/p>\nAnd there you have it!<\/h3>\n