Given two dates like the following
Feb-14-07 18:20:11
Mar-03-07 20:32:19
how can I calculate, with an Excel formula or macro, the number of
days (with decimals!) between the first and the second?

=A2-A1 and format General
or use DATEDIF (undocumented function) - details at
http://www.cpearson.com/excel/datedif.aspx
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Luca Villa" wrote in message
ups.com...
Given two dates like the following
Feb-14-07 18:20:11
Mar-03-07 20:32:19
how can I calculate, with an Excel formula or macro, the number of
days (with decimals!) between the first and the second?

Bernard Liengme ha scritto:

=A2-A1 and format General

it gives "#VALORE!" (my Excel is in italian), perhaps because the
format of the dates is not recognized. I also tried to browse the
various date formats even english but I didn't find one that coincide.


or use DATEDIF (undocumented function) - details at
http://www.cpearson.com/excel/datedif.aspx
best wishes

I tried =DATEDIF(Date1, Date2, Interval) (precisely =DATEDIF(A1, A2,
d) but it doesn't accept the formula, selecting "A1, A2, d" as if it
doesn't accept them.

I suspect that Microsoft likes to translate the formula commands in
addition to the program interface!

In English this is a VALUE error that results from trying to do arithmetic
on text. You may need to re-enter the dates to ensure they are in real date
format.
To check this, format on of your date cells as Number; it should display as
a number close to 39,000. Tell us what you get.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Luca Villa" wrote in message
ups.com...

Bernard Liengme ha scritto:

=A2-A1 and format General

it gives "#VALORE!" (my Excel is in italian), perhaps because the
format of the dates is not recognized. I also tried to browse the
various date formats even english but I didn't find one that coincide.


or use DATEDIF (undocumented function) - details at
http://www.cpearson.com/excel/datedif.aspx
best wishes

I tried =DATEDIF(Date1, Date2, Interval) (precisely =DATEDIF(A1, A2,
d) but it doesn't accept the formula, selecting "A1, A2, d" as if it
doesn't accept them.

I suspect that Microsoft likes to translate the formula commands in
addition to the program interface!

I second that. For the dates, I entered:
02/14/2007 18:20
03/03/2007 20:32
in A1 and A2, then entered a simple formula "=A2-A1" and formatted the
formula cell as Number with 6 digits.
The answer is 17.091759

Cheers,
-Basilisk96


On Nov 11, 8:56 am, "Bernard Liengme"
wrote:
In English this is a VALUE error that results from trying to do arithmetic
on text. You may need to re-enter the dates to ensure they are in real date
format.
To check this, format on of your date cells as Number; it should display as
a number close to 39,000. Tell us what you get.
best wishes
--
Bernard V Liengme
Microsoft Excel MVPwww.stfx.ca/people/bliengme
remove caps from email

"Luca Villa" wrote in message

ups.com...



Bernard Liengme ha scritto:

=A2-A1 and format General

it gives "#VALORE!" (my Excel is in italian), perhaps because the
format of the dates is not recognized. I also tried to browse the
various date formats even english but I didn't find one that coincide.

or use DATEDIF (undocumented function) - details at
http://www.cpearson.com/excel/datedif.aspx
best wishes

I tried =DATEDIF(Date1, Date2, Interval) (precisely =DATEDIF(A1, A2,
d) but it doesn't accept the formula, selecting "A1, A2, d" as if it
doesn't accept them.

I suspect that Microsoft likes to translate the formula commands in
addition to the program interface!

There is not a date format like "Feb-14-07 18:20:11" in my Excel. I
use Excel 2007 and it has many different date formats, including a
"3/14/01 13.30" when I choose the local English (USA), but I can't
find one like "Feb-14-07 18:20:11".

I even tried to reproduce the example below without success:

Basilisk96 ha scritto:

I second that. For the dates, I entered:
02/14/2007 18:20
03/03/2007 20:32
in A1 and A2, then entered a simple formula "=A2-A1" and formatted the
formula cell as Number with 6 digits.
The answer is 17.091759

Basilisk, what cell format (and date format) did you set for those
dates?
can you also use "Feb-14-07 18:20:11" with success?

Another option is the Documented function DAYS360(EarlierDate, LaterDate).
There is also a function in one of the AddIns that will give the number of
workdays between two dates, counting a 5 day workweek. I was unable to find
it at the moment, but I know it's out there.

"Bernard Liengme" wrote:

=A2-A1 and format General
or use DATEDIF (undocumented function) - details at
http://www.cpearson.com/excel/datedif.aspx
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Luca Villa" wrote in message
ups.com...
Given two dates like the following
Feb-14-07 18:20:11
Mar-03-07 20:32:19
how can I calculate, with an Excel formula or macro, the number of
days (with decimals!) between the first and the second?

Found it!
You have to load the Analysis Toolpack addin that is shipped with Excel, but
not automatically loaded (until Excel 2007). Once you have the toolpack
loded you can use the function

NETWORKDAYS(FirstDate,LastDate,ListOfHolidaysTop:L istOfHolidaysBottom)

This will give you the number of workdays excluding weekends and holidays in
the list you specify.

I have not tried it with the time information included, but you could always
just subtract the time part of the entry and add the time part to the number
of days returned.

Van!!
"Luca Villa" wrote:

Given two dates like the following
Feb-14-07 18:20:11
Mar-03-07 20:32:19
how can I calculate, with an Excel formula or macro, the number of
days (with decimals!) between the first and the second?

On Sun, 11 Nov 2007 04:35:01 -0800, Ropebender
wrote:

Found it!
You have to load the Analysis Toolpack addin that is shipped with Excel, but
not automatically loaded (until Excel 2007). Once you have the toolpack
loded you can use the function

NETWORKDAYS(FirstDate,LastDate,ListOfHolidaysTop: ListOfHolidaysBottom)

This will give you the number of workdays excluding weekends and holidays in
the list you specify.

I have not tried it with the time information included, but you could always
just subtract the time part of the entry and add the time part to the number
of days returned.

Van!!
"Luca Villa" wrote:

Given two dates like the following
Feb-14-07 18:20:11
Mar-03-07 20:32:19
how can I calculate, with an Excel formula or macro, the number of
days (with decimals!) between the first and the second?



Of course, neither DAYS360 nor NETWORKDAYS return the number of days, with
fractions, between two date/time entries, which is what was requested.
--ron