Excel Calculate the difference by date from today from the cell "7/6/2012 10:26:42"

Question

So, I have a cell with the entered on 7/6/2012 10:26:42 , I want to show the date difference today in another cell.

I tried to extract on 7/6/2012 using =LEFT(A1, Find(" ", A1, 1) -1) , but I got a value error.

The formula works when I do A1 '7/6/2012 10:26:42 , however it is not perfect because I need to work with the whole column.

Answer 1

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

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

Quote from excel.datedif.com

The mysterious dateif function in Microsoft Excel

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

This feature is available in all versions of Excel, but is not documented. It is not even listed in the Insert Function dialog box. Therefore, you must enter it manually in the formula field. Syntax

DATEDIF( start_date, end_date, interval_unit )

start_date from date end_date to date (must be after start_date) interval_unit The unit to be used for the output interval Values ​​for interval_unit

interval_unit Description

D Number of days

M Number of full months

Y Number of full years

YD Number of days excluding years

MD Number of days excluding months and years

YM Number of months excluding years

Mistakes

Error description

#NUM! End_date later (more) start_date or interval_unit has an invalid value. #COST! end_date or start_date is not valid.

Answer 2

If this is a valid date / time record, then excel just saves it as a number (days are integers and time is decimal), so you can do a simple subtraction.

I'm not sure if the 7/6 is June 7 or July 6, assuming the latter is a future date so that you can split the days from

=INT(A1-TODAY())

Make sure you format the result cell as a total or a digit (not a date)

Answer 3

For the difference between the date A1 and Today, you can enter: = ABS (TODAY () - A1)

which returns the (fractional) number of days between dates.

You will probably get #CURRENCIES! an error in your formula because Excel treats dates as numbers.

Answer 4

Why don't you just make it easy and simple. If I need to know the number of days between today and say March 10, 2015, I can simply enter a simple formula.

Let's say the static date is March 10, 2015 and is located in cell O5.

The formula for determining the number of days between today and O5 will be: = O5-Today ()

Nothing out of the ordinary or DATEDIF. Obviously, the cell into which you enter this formula must have the data type "number". Just enter the date, usually in the control cell, in this case O5.

Answer 5

=ROUND((TODAY()-A1)/365,0) will provide the number of years between the date in cell A1 and today's date

Answer 6

* In all cases # refers to the cell number

You really don't need dateif functions; eg:

I am working on a table that tracks employee benefits.

I have hiring dates in column "A" and in column B = (TODAY () - A #)

And you just format the cell to display the total instead of the date.

It also works very differently very easily: I also converted this number to showing when the actual date is that they get their benefits, and not how many days are left, but it's just

= (90-B #) + TODAY ()

Just make sure you format the cells as totals or dates, respectively.

Hope this helps.

Answer 7

 DAYS(start_date,end_date): 

For instance:

 DAYS(A1,TODAY())