How do I find days between two dates?

B

Brett

I'm using this formula
=TODAY()-(7/1/2004)
to find the days between today (4/2) and 7/1. The result is
38079.99651. Why doesn't this give the days?

Also, is there a way to display the number of months and days in the
same cell? For example
=MONTH(TODAY())-MONTH(7/1/2004)
with the day formula and have the result read "2 months 28 days" or
so?

Thanks,
Brett
 
D

Dave Peterson

Try:

=today()-date(2004,1,7)

date() is y,m,d--did you mean July 1, 2004 or January 7, 2004?

And format the cell as General.

7/1/2004 looks like arithmetic (7 divided by 1 divided by 2004).

And when you format a date as general, you'll see that excel is just counting
the number of days since a base date (usually dec 31, 1899).
 
N

Norman Harker

Hi Brett!

Dave and Robert have shown you the way, but let's look at where you
were going wrong.

=TODAY-(7/1/2004)

The date needs to be in "" otherwise it is interpreted as 7 divided by
1 divided by 2004

This gives you:

=TODAY()-"7/1/2004"
Format General
Returns 88

Now that might be the wrong answer because your date settings are
different from mine. I have dd-mm-yyyy. To get an answer that is
correct for all date settings use:

=TODAY()-"7-Jan-2004"
Format General
Returns 88

Or:
=TODAY()-"1-Jul-2004"
Format General
Returns ########

It returns ##### because the 1900 Date System can't handle negative
dates. To get this working:

=IF(TODAY()<"1-Jul-2004","1-Jul-2004"-TODAY(),TODAY()-"1-Jul-2004")

In general terms you are discouraged from entering dates as strings
and it is much better to use the unambiguous DATE function as the mode
of entry.

However, if you want to use a string entry, always enclose in " and
always give an unambiguous form such as dd-mmm-yyyy

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
B

Brett

Norman, Thanks. Suppose I want the number of days between now and
10/1/2004. I use
=("10/1/2004"-TODAY())/30
which gives 6

If I use
=("7/1/2004"-TODAY())/30
that gives 2.9333333

These are formatted as general. I'm trying to figure the number of
months between these dates. I'm using 30 as an average number of days
per month. Would 30.5 be better? That gives 5.9016 for 10/1/2004,
which seems more correct than 6. Does Excel offer some type of
function that I should use and determine the days for each month
between the difference?

Also, if 7/1/2004 and 7-Jan-2004 both return 88 below, why do you say
to use the latter?

Thanks,
Brett
 
B

Bob Robertson

I use the NETWORKDAYS Function to find the number of days between two dates.
=NETWORKDAYS(Start_date,End_date,Holidays)
The holidays is optional and may be left out.

For your problem =NETWORKDAYS("07/01/2004",Today()) will give you the
correct answer.

This function is part of the Analysis Toolpack which can be loaded from the
menu Tools / Add-Ins just click the selection for the toolpack.
I hope this helps.

Bob Robertson
 
N

Norman Harker

Hi Brett!

For number of months between dates use DATEDIF.

=DATEDIF("7-Jan-2004",TODAY(),"m")

For details of the mysterious DATEDIF see:

Chip Pearson:
http://www.cpearson.com/excel/datedif.htm

In general terms where you are using strings for dates use
"dd-mmm-yyyy" because it will be interpreted the same irrespective of
your Regional settings.

"7-1-04" can be interpreted no less than 6 different ways depending
upon your Regional settings of country and / or the double digit date
interpretation. "7-1-2004" can be interpreted 2 different ways.
"7-Jan-2004" will only be interpreted one way.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

Norman Harker

Hi Bob!

From the examples given it appears Brett wants "gross" days between
dates and not working days.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
B

Brett

That's right - I want to include all days. DATEDIF doesn't do what I
need. Using your example,

=DATEDIF("7-Jan-2004",TODAY(),"m")

the result is 2. I want it carried out and exact. It should be
something similar to 2.856 or what ever. That's why I'm dividing by
30.5 in the above examples. Most months alternative between 30 and 31
days. Using a number of days would be exact but I need it by month

Thanks,
Brett
 
N

Norman Harker

Hi Brett!

This starts to get complicated!

You can use:

=DATEDIF("7-Jan-2004",TODAY(),"m")+DATEDIF("7-Jan-2004",TODAY(),"md")/DAY(EOMONTH(TODAY(),0))

It's made up of three parts:

=DATEDIF("7-Jan-2004",TODAY(),"m")
Returns the number of whole months

=DATEDIF("7-Jan-2004",TODAY(),"md")
Returns the balancing number of whole days

=DAY(EOMONTH(TODAY(),0))
Returns the number of days in the month in which TODAY() falls.

Regarding the denominator of the fraction to derive the decimal part
of a month, it seems logical to use the terminating month as the
number of days to use.

But you will still get unusual results with, for example, comparisons
of 31-Jan and 1-Mar.

For this reason, I much prefer using weeks or days as they both have
constant lengths.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Top