Excel Calculate the date difference from today from a cell of 7 6 2012 10 26 42

Question

So I have a cell with 7 6 2012  10 26 42 inputted  I want to show the date difference from today in another cell   I tried to extract 7 6 2012 with  LEFT A1  Find      A1  1  -1  but turned out theres a value error   The formula works when I make A1  7 6 2012  10 26 42  however it is not ideal because I have to work with the whole column

User · Answer

You can use the datedif function to find out difference in days.

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

Quote from excel.datedif.com

The mysterious datedif function in Microsoft Excel

The Datedif function is used to calculate interval between two dates in days, months or years.

This function is available in all versions of Excel but is not documented. It is not even listed in the "Insert Function" dialog box. Hence it must be typed manually in the formula box. Syntax

DATEDIF( start_date, end_date, interval_unit )

start_date from date end_date to date (must be after start_date) interval_unit Unit to be used for output interval Values for interval_unit

interval_unit Description

D Number of days

M Number of complete months

Y Number of complete years

YD Number of days excluding years

MD Number of days excluding months and years

YM Number of months excluding years

Errors

Error Description

#NUM! The end_date is later than (greater than) the start_date or interval_unit has an invalid value. #VALUE! end_date or start_date is invalid.

User · Answer

DAYS start date end date     For example   DAYS A1 TODAY

User · Answer

ROUND  TODAY  -A1  365 0  will provide number of years between date in cell A1 and today s date

User · Answer

Why don t you just make it easy and simple   If I need to know the number of days between today and say  March 10th  2015  I can just enter the simple formula    Lets say the static date is March 10th  2015  and is in cell O5   The formula to determine the number of days between today and O5 would be   O5-Today    Nothing fancy or DATEDIF stuff   Obviously  the cell where you type this formula in must have a data type of  number    Just type your date in normally in the reference cell  in this case O5

User · Answer

For the difference between A1 and Today s date you could enter         ABS TODAY  -A1   which returns the  fractional  number of days between the dates   You re likely getting a  VALUE  error in your formula because Excel treats dates as numbers

User · Answer

If that s a valid date time entry then excel simply stores it as a number  days are integers and the time is the decimal part  so you can do a simple subtraction   I m not sure if 7 6 is 7th June or 6th July  assuming the latter then it s a future date so you can get the difference in days with   INT A1-TODAY     Make sure you format result cell as general or number  not date

User · Answer

In all instances the   refers to the cell number  You really don t need the datedif functions  for example   I m working on a spreadsheet that tracks benefit eligibility for employees   I have their hire dates in the  A  column and in column B is   TODAY  -A    And you just format the cell to display a general number instead of date   It also works very easily the other way  I also converted that number into showing when the actual date is that they get their benefits instead of how many days are left  and that is simply    90-B   TODAY    Just make sure you re formatting cells as general numbers or dates accordingly   Hope this helps

[excel] Excel Calculate the date difference from today from a cell of "7/6/2012 10:26:42"

Errors

Examples related to excel

Examples related to date