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.

Answer 1

The following seemed to work well for me:

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

Answer 2

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]

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.