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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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!

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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: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?
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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" <blien...@stfx.TRUENORTH.ca>
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" <lucavi...@cashette.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > 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?

I wonder if you have the dates the wrong way around!
If you have =A1-A2 and A1 is smaller (earlier) than A2 you get a negative
date that Excel cannot work with.
So you need =A2-A1
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Luca Villa" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>

This is what I do:

I change the cell format of the entire A column to "date" -
"03/14/2001" (there isn't a format identical to "02/14/2007 18:20").
I type "02/14/2007 18:20" in A1 and I press enter. The date stay
aligned on the left.
I type "03/03/2007 20:32" in A2 and I press enter. Now something
strange happen: while the date I entered is apparently like that in A1
here the date gets aligned to the right and automatically changes to
"3/3/2007".
I change the cell format of B1 to numeric.
I type "=A2-A1" in B1 and I press enter.
B1 contains "#VALORE!"