Number of days between past date and current date in Google spreadsheet

48
votes

I want to calculate the number of days passed between past date and a current date. My past date is in the format dd/mm/yyyy format. I have used below mentioned formulas but giving the proper output.

=DAYS360(A2,TODAY())
=MINUS(D2,TODAY())

In the above formula A2 = 4/12/2012 (dd/mm/yyyy) and I am not sure whether TODAY returns in dd/mm/yyyy format or not. I have tried using 123 button on the tool bar, but no luck.

6
dategoogle-sheetsdatediff
actually, stackoverflow makes google results better. or rather, we at SO do. :) – törzsmókus
(my first comment reflected to a section of the question that has been edited out since) – törzsmókus

6 Answers

42
votes

The following seemed to work well for me:

=DATEDIF(B2, Today(), "D")
39
votes

DAYS360 does not calculate what you want, i.e. the number of days passed between the two dates – see the end of this post for details.

MINUS() should work fine, just not how you tried but the other way round:

=MINUS(TODAY(),D2)

You may also use simple subtraction (-):

=TODAY()-D2

I made an updated copy of @DrCord’s sample spreadsheet to illustrate this.

Are you SURE you want DAYS360? That is a specialized function used in the financial sector to simplify calculations for bonds. It assumes a 360 day year, with 12 months of 30 days each. If you really want actual days, you'll lose 6 days each year. [source]

4
votes

Since this is the top Google answer for this, and it was way easier than I expected, here is the simple answer. Just subtract date1 from date2.

If this is your spreadsheet dates

     A            B
1 10/11/2017  12/1/2017

=(B1)-(A1)

results in 51, which is the number of days between a past date and a current date in Google spreadsheet

As long as it is a date format Google Sheets recognizes, you can directly subtract them and it will be correct.

To do it for a current date, just use the =TODAY() function.

=TODAY()-A1

While today works great, you can't use a date directly in the formula, you should referencing a cell that contains a date.

=(12/1/2017)-(10/1/2017) results in 0.0009915716411, not 61.

3
votes

I used your idea, and found the difference and then just divided by 365 days. Worked a treat.

=MINUS(F2,TODAY())/365

Then I shifted my cell properties to not display decimals.

1
votes

If you are using the two formulas at the same time, it will not work... Here is a simple spreadsheet with it working: https://docs.google.com/spreadsheet/ccc?key=0AiOy0YDBXjt4dDJSQWg1Qlp6TEw5SzNqZENGOWgwbGc If you are still getting problems I would need to know what type of erroneous result you are getting.

Today() returns a numeric integer value: Returns the current computer system date. The value is updated when your document recalculates. TODAY is a function without arguments.

-2
votes

  1. Today() does return value in DATE format.

  2. Select your "Days left field" and paste this formula in the field =DAYS360(today(),C2)

  3. Go to Format > Number > More formats >Custom number format and select the number with no decimal numbers.

I tested, it works, at least in new version of Sheets, March 2015.