• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

No. of days lapsed from a gvien date

Ushasri


It is as simple as =Date2-Date1

Assume:

A1 has 1/6/2012

A2 has =Today()


A3=A2-A1

= 38 (for today only)


You can also use

=date(2012,7,9)-Date(2012,6,1)


Dates are just Integer Numbers which use Day 1 as 1/1/1900

So today 9/7/2012 is day 41099


Dates are only special in that they use a Different Display format than regular numbers
 
Can you explain why it didn't work and maybe post some data/values or a sample file
 
Ushasri

[pre]
Code: 
Hui's function works as it should, even gives the negative number of days for future days,
I think the reason you only get it to work on one cell is because you have not put
the function in any other cell !!!
[/pre]
https://dl.dropbox.com/u/75495784/TimeToday.xlsx
 
Yes, the formula worked as I said,

but it can't be dragged down the column.


considering the example below:

It is as simple as =Date2-Date1

Assume:

A1 has 1/6/2012

A2 has =Today()


A3=A2-A1

= 38 (for today only)


A1 is different in each row.How to make the formula consider a different A1 and same A2 dates in every row.


Suggest please.


Thanks.
 
Ushasri


If you use

A1 has 1/6/2012

A2 has =Today()


A3=A2-A1


You can't copy that down, but you can copy it accross


If you want to work with data in columns


A1 has 1/6/2012

B1 has =Today()


C1=B1-A1


then copy down


Your original question never stated you had data in Rows or Columns and so i only gave you an example formula.

You need to adjust it to make it fit
 
Hi Hui,


I tried your suggestion, the result of the formula displays ########### saying dates and times too large or negative ;

manual calculation says it is only above 90 days i am calculating...... for first cell.


Drop down seems impossible here

suggest if there could be a mistake in format of dates


7/11/2012 minus 12/04/2012

7/11/2012 minus 29/03/2012


Take the above example.


bye
 
@ushasri.polavarapu

Have you tried DATEDIF() Function?

A1 has 1/Jun/2012

A2 has =Today() or 11/Jul/2012


=DATEDIF(A1,A2,"D") 'for difference in Days
 
Syntax of the formula


DATEDIF(<Previous Date>,<Later Date>,<"Type of difference">


Type of difference:

If you want to differ in Days then "D"

If you want to differ in Months then "M"

If you want to differ in Year then "Y"

=DATEDIF(A1,A2,"D") 'for difference in Days

Any Question?
 
ushasri.polavarapu the spread sheet I uploaded uses Hui's formula and is copied down, and works as the spread sheet shows with different dates
 
Hi, nazmul_muneer, ushasri.polavarapu!


I'd suggest you to not use DATEDIF function with the parameter "M". It has bugs that are not fixed even in 2010 version. Being a useful function it has been so poorly implemented that the last time Microsoft included it in the official documentation was in Excel 2000; after that it disappeared from the scene. The unrecognized bug is the answer.


This issue has been widely analyzed here, give a look at the first link which leads you to the second link where it's discussed and proved working wrong. You'll find there a simple two-pass workaround.


http://chandoo.org/forums/topic/calculation-of-of-months-between-end-of-year-and-a-given-inputted-date#post-17696

http://chandoo.org/forums/topic/excel-2007


Regards!
 
You must log in or register to reply here.
Back
Top