CODEWITHSUNDEEP
dategoogle-sheetsgoogle-sheets-formuladatediff
Vikram
Vikram

Reputation: 7517

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

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.

Upvotes: 56

Views: 184270

Answers (7)

Matthew Clayton
Matthew Clayton

Reputation: 631

This worked for me:

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

Upvotes: 53

Jane Kathambi
Jane Kathambi

Reputation: 925

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")

Upvotes: 3

törzsmókus
törzsmókus

Reputation: 2003

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]

Upvotes: 47

Joshua Dance
Joshua Dance

Reputation: 10472

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.

Upvotes: 5

user5428882
user5428882

Reputation: 31

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.

Upvotes: 3

Webmaster
Webmaster

Reputation: 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.

Upvotes: -4

DrCord
DrCord

Reputation: 3955

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.

Upvotes: 1

Related Questions