Difference between dates

[Erika]

Hi, people. My excel is rusty and I need help.

I have a new project and I'm setting up tasks in a spreadsheet. I want
to have a date in the corner that is captioned: X (next cell: Days to
deadline). I googled but can't make the answers work right.

I tried this formula and get an error that says #NUM!
=DateDif(now(),11/1/2005,"d")

I can do it this way but it's stupid. Why waste space with a1 and a2?
=DateDif(a1,a2,"D")

What am I doing wrong? Thanks for the help!

 

[David McRitchie]

Hi Erika,
The safest way to specify a date in Excel is DATE(year,month,day)
then you have no trouble with other date formats outside US.

You will probably find examples at
http;//www.cpearson.com/excel/datedif.htm
http://www.cpearson.com/excel/datetime.htm
http://www.mvps.org/dmcritchie/excel/datetime.htm

 

[Ron Rosenfeld]

Hi, people. My excel is rusty and I need help.

I have a new project and I'm setting up tasks in a spreadsheet. I want
to have a date in the corner that is captioned: X (next cell: Days to
deadline). I googled but can't make the answers work right.

I tried this formula and get an error that says #NUM!
=DateDif(now(),11/1/2005,"d")

I can do it this way but it's stupid. Why waste space with a1 and a2?
=DateDif(a1,a2,"D")

What am I doing wrong? Thanks for the help!

11/1/2005 gets parsed as 11 divided by 1 divided by 2005 or some number less
than 1. Translated to a date that is either 1/0/1900 or 1/1/1904 (depending on
the date system you are using). Since d2 is earlier than d1, DATEDIF gives an
error.

Better:

=DATEDIF(TODAY(),"11/1/2005","d")

even better:

=DATEDIF(TODAY(),DATE(2005,11,1),"d")

and simpler (but you must be sure to format the cell as General or Number after
entering the formula):

="11/1/2005" - TODAY()

or

=DATE(2005,11,1) - TODAY()

Note the use of TODAY() rather than NOW(). NOW() will included a time; TODAY()
just includes the date.

So far as putting your variables into cells, and then referencing those cells,
the advantage is that it makes it much easier to change the variables rather
than having to edit the formula. And there are 16,777,216 cells per worksheet
so using 2 would probably still leave you enough for your other information


--ron

 

[Erika]

11/1/2005 gets parsed as 11 divided by 1 divided by 2005 or some number less
than 1. Translated to a date that is either 1/0/1900 or 1/1/1904 (depending on
the date system you are using). Since d2 is earlier than d1, DATEDIF gives an
error.

Better:

=DATEDIF(TODAY(),"11/1/2005","d")

even better:

=DATEDIF(TODAY(),DATE(2005,11,1),"d")

and simpler (but you must be sure to format the cell as General or Number after
entering the formula):

="11/1/2005" - TODAY()

or

=DATE(2005,11,1) - TODAY()

Note the use of TODAY() rather than NOW(). NOW() will included a time; TODAY()
just includes the date.

So far as putting your variables into cells, and then referencing those cells,
the advantage is that it makes it much easier to change the variables rather
than having to edit the formula. And there are 16,777,216 cells per worksheet
so using 2 would probably still leave you enough for your other information


--ron

=DATE(2005,11,1) - TODAY()
this works great. Thanks a lot for the solution, and thanks for the
explanation also.

I realize I have plenty of cells available. ;-) Is it so eccentric to
prefer an elegant, minimalist solution?

 

[Erika]

Hi Erika,
The safest way to specify a date in Excel is DATE(year,month,day)
then you have no trouble with other date formats outside US.

You will probably find examples at
http;//www.cpearson.com/excel/datedif.htm
http://www.cpearson.com/excel/datetime.htm
http://www.mvps.org/dmcritchie/excel/datetime.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I tried this formula and get an error that says #NUM!
=DateDif(now(),11/1/2005,"d")

thanks for your comment, David.

actually the pages you quoted came up in my google search and that's
where I got the above formula, that didn't work for me. (BTW, the first
reference to datedif isn't a good link b/c there's a semicolon after http.)

will bookmark your excel page for future help!

 

[Ron Rosenfeld]

I realize I have plenty of cells available. ;-) Is it so eccentric to
prefer an elegant, minimalist solution?

So long as you don't have to alter your formula, it makes little difference.


--ron

 

[David McRitchie]

HI Erika,
Thanks for the correction. I see in Outlook Express it didn't turn blue
like the other links. My usual kind of typo is leaving out the word the "not".