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

Question

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.

Conrad

(my first comment reflected to a section of the question that has been edited out since) – Helterskelter

Answer 1

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]

Ogre

It would be more helpful to write the formula in your answer – Ataraxia

@BenWheeler Thanks for the suggestion, I have updated my old answer – Helterskelter

Answer 2

This worked for me:

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

Postmillennialism

Documentation with more examples of DATEDIF can be found here. Keep in mind that you may need to format the cell to be a number (by default it seems to output a date.) "Format" (from top level menu) > "Number" > "More Formats" > "Custom number formats" > "0" – Cuneiform

thats the best solution. – Langer

Answer 3

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.

Beckmann

this method is (and was) already mentioned in my accepted answer ¯_(ツ)_/¯ – Helterskelter

Answer 4

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.

Wirephoto

Answer 5

The following worked for me. Kindly note that TODAY() must NOT be the first argument in the function otherwise it will not work.

=DATEDIF( W2, TODAY(), "d")

Phototypy

Answer 6

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.

Throckmorton

Answer 7

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.

Socialistic