How to calculate difference between dates.

[ThrottleWorks]

I have of column for dates, I need to calculate difference between the date in column & today’s date.


For example in cell A1 I have a date as 30/6/2012 & today is 22/6/2012.

I want to calculate the difference as 30/6/2012 - 22/6/2012.


My problem is the value in the cell A1 is in date format, when I am trying to enter the formula A1- today () it is giving me #value.


I tried to substitute “/” with “” and got the value as 300612. I multiply it with 1 to convert the value in number format.

But when I calculated the difference with this value I am getting 259530.


Can anyone help me in this please, sorry for the long post.

 

[Montrey]

Use =datedif(begindate,enddate,"d")


replace "d" with "m" if you want months.

Here is more info on the formula.

http://www.cpearson.com/excel/datedif.aspx

 

[NARAYANK991]

Hi Sachin ,


My computer date format is mm/dd/yy.


I entered a date in A1 , in the correct date format as 6/30/2012. Then in another cell , I entered the formula =A1-TODAY() , and I got the correct result of 8.


When I changed the cell A1 to 30/06/2012 , the same formula gave me a #VALUE! error , because Excel no longer recognized this as a valid date.


Narayan

 

[ThrottleWorks]

@ Monetrey @ Narayan - Sir thanks a lot for your help, however the dateddiff also gave me #value.


I think the reason must be what Narayan sir is sayinng, the problem is how can changes the date format for the entire column, typing dates manually in different column is time consuming.


I am unable to change the format of existing values, I tried changing the format with contral 1 (format cells) to mm/dd/yy, but this will not changes existing values.


Can you please help me in this

 

[NARAYANK991]

Hi Sachin ,


Use the following formula to convert the existing dates in an invalid format , to dates in a valid format , assuming that your valid date format is mm/dd/yyyy.

[pre]

=DATEVALUE(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&RIGHT(A1,4))
or

[pre][code]=DATEVALUE(RIGHT(A1,4)&"/"&MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1))

[/pre]
or

=DATE(RIGHT(A1,4),MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1),LEFT(A1,FIND("/",A1)-1))[/code][/pre]
All the above formulae will convert a date , say 30/6/2012 , which is not a valid date in the mm/dd/yyyy format , to 06/30/2012.


Narayan

 

[Montrey]

GJ Narayan!

 

[ThrottleWorks]

Sir, thanks a lot for your reply, i do not know what mistake I am doing, but this is still not working for me.... 1st formula gave me #value & 2nd formula gave me #num.


If I am not wrong this formula should give me vlaue as 06/30/2012 but somehow excel is not happy & is genarating #values for me.


Anyway, I really want to thank you honestly for your valuable support & time.

 

[NARAYANK991]

Hi ,


Thanks Montrey , but it's premature !


All of the above formulae work only if the entry in A1 is NOT a valid date ; if a value such as 03/03/2012 , which is valid in both formats , is entered , then the above formulae will return the same #VALUE! error !


Need to work on it some more.


Narayan


Sachin : is it possible you can give some samples , one of a valid date in your worksheet , and one of the data which is giving you the errors ?

 

[ThrottleWorks]

Narayan Sir & Montrey Sir, thanks a lot for your help. I am sure I am doing something wrong & not explaining the problem properely. I am re-trying on the formulas suggested by you.