Calculate the number of days between two dates
 

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?

Calculate the number of days between two dates
 

=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?

Calculate the number of days between two dates
 

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!

Calculate the number of days between two dates
 

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?

Calculate the number of days between two dates
 

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?

Calculate the number of days between two dates
 

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!

Calculate the number of days between two dates
 

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!

Calculate the number of days between two dates
 

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?

Calculate the number of days between two dates
 

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" wrote in message
ups.com...
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?

Calculate the number of days between two dates
 

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!"

Calculate the number of days between two dates
 

Dates are right aligned. It would seem you have the date format set to the
European system dd/mm/yyyy rather than the US one of mm/dd/yyyy. This
explains why the first one gets aligned to the right - it is treated as
text. Like most Canadians, I use dd/mm/yyyy so if I type 02/14/2007 in a
cell I get text.

What date format do you wish to use, dd/mm/yyyy or mm/dd/yyyy? You can use
Format | Format Cell ; specify Custom and set either dd/mm/yyyy h:mm or
mm/dd/yyyy h:mm

Try again and tell us what happens
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Luca Villa" wrote in message
ups.com...
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!"

Calculate the number of days between two dates
 

Bernard,

You was right when you wrote that the problem was in the date format.
Indeed the alginment seems to change as soon as I put a number higher
than 12 in the second field of the date.
But the bad problem is that even if I prepare a custom cell format
like "mm/dd/yyyy hh:mm" it persists to align it on the left as soon as
I put a number higher than 12 in the second field.

Anyway, the real data format that I need is "Feb-02-07 20:36:50".
Do you know how I can define it in the custom cell format?

Calculate the number of days between two dates
 

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

Calculate the number of days between two dates
 

On Sun, 11 Nov 2007 16:30:20 -0800, Luca Villa wrote:

Bernard,

You was right when you wrote that the problem was in the date format.
Indeed the alginment seems to change as soon as I put a number higher
than 12 in the second field of the date.
But the bad problem is that even if I prepare a custom cell format
like "mm/dd/yyyy hh:mm" it persists to align it on the left as soon as
I put a number higher than 12 in the second field.

Anyway, the real data format that I need is "Feb-02-07 20:36:50".
Do you know how I can define it in the custom cell format?

Luca,

The date format that you set in Excel applies ONLY to the manner in which an
entry is displayed.


When you enter a date into Excel, however, it is interpreted according to the
short date format set under your Windows regional settings in the Control Panel
(external to Excel).

So one method of being able to ENTER your dates in the format you wish is to go
to Start/Control Panel/Regional Settings and change your date format there.
--ron

Calculate the number of days between two dates
 

Ah Ron, you are totally right!
After I changed the Regional Settings to USA I can calculate A2-A1
with the dates in the "3/14/2007 20:32" format!

Now the remaining problem is to make it accept the "Oct-29-07
22:36:59" format.
Any ideas?

Calculate the number of days between two dates
 

On Nov 12, 1:27 pm, Luca Villa wrote:
Ah Ron, you are totally right!
After I changed the Regional Settings to USA I can calculate A2-A1
with the dates in the "3/14/2007 20:32" format!

Now the remaining problem is to make it accept the "Oct-29-07
22:36:59" format.
Any ideas?

I can make it work with the custom format:
mmm-dd-yy hh:mm:ss
and the date math still works as expected.

This displays the date as "Oct-29-07 22:36:59", but I have found that
I must enter it into the cell as 10/29/07 22:36:59, otherwise it is
not recognized as a date if I enter "Oct-29-07". My regional settings
are USA, with the Short Date format set to "MM/dd/yyyy". I have
experimented with changing that to "MMM-dd-yy", without success in
Excel. Even tried restarting my PC - still Excel does not recognize
"Oct-29-07" as a date when entered like that.

Not sure what is happening here. When I select a cell with a date
already entered, the formula bar shows the date format using the
Regional Short Date format (although for some reason it shows
"10-29-07" instead of the expected "Oct-29-07").

Cheers,
-Basilisk96

Calculate the number of days between two dates
 

On Mon, 12 Nov 2007 10:27:57 -0800, Luca Villa wrote:

Ah Ron, you are totally right!
After I changed the Regional Settings to USA I can calculate A2-A1
with the dates in the "3/14/2007 20:32" format!

Now the remaining problem is to make it accept the "Oct-29-07
22:36:59" format.
Any ideas?

If you want to display your date in that format, just use the custom format:

mmm-dd-yy hh:mm:ss

If you want to be able to enter the date in that format, you will have to
translate it into something Excel can understand as a date.

For example, one way would be:

=--SUBSTITUTE(SUBSTITUTE(A1,"-"," ",1),"-",", ")

and format the result as you want.
--ron

Calculate the number of days between two dates
 

What about a formula that translates the English month names into
month numbers (and uses the Italian names of the functions)?

I'm trying with this but it doesn't accept the matrix with the names
of the months. What' the correct way to write it?

=VALORE("20"&STRINGA.ESTRAI(A2,8,2)&"/"&CONFRONTA(SINISTRA(A2,3),
{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"},
0)&"/"&STRINGA.ESTRAI(A2,5,2))+VALORE(DESTRA(A2,8))-
VALORE("20"&STRINGA.ESTRAI(A1,8,2)&"/"&CONFRONTA(SINISTRA(A1,3),
{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"},
0)&"/"&STRINGA.ESTRAI(A1,5,2))+VALORE(DESTRA(A1,8))

Kevin

Calculate the number of days between two dates
 

On Tue, 13 Nov 2007 04:25:10 -0800, Luca Villa wrote:

What about a formula that translates the English month names into
month numbers (and uses the Italian names of the functions)?

I'm trying with this but it doesn't accept the matrix with the names
of the months. What' the correct way to write it?

=VALORE("20"&STRINGA.ESTRAI(A2,8,2)&"/"&CONFRONTA(SINISTRA(A2,3),
{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug", "Sep","Oct","Nov","Dec"},
0)&"/"&STRINGA.ESTRAI(A2,5,2))+VALORE(DESTRA(A2,8))-
VALORE("20"&STRINGA.ESTRAI(A1,8,2)&"/"&CONFRONTA(SINISTRA(A1,3),
{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug", "Sep","Oct","Nov","Dec"},
0)&"/"&STRINGA.ESTRAI(A1,5,2))+VALORE(DESTRA(A1,8))

Kevin

I would think that:

=--SUBSTITUTE(SUBSTITUTE(A1,"-"," ",1),"-",", ")

is a lot shorter! And also results in an Excel recognized date that can be
used in calculations.

An alternative would be to use a routine that would convert "Oct-29-07
22:36:59" to a proper Excel date no matter what the Regional Settings:

=DATE(MID(A1,8,2)+1900+100*(--MID(A1,8,2)<31),
MATCH(LEFT(A1,3),{"Jan";"Feb";"Mar";"Apr";"May";"J un";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),
MID(A1,5,2))+ RIGHT(A1,8)

--ron

Calculate the number of days between two dates
 

=--SUBSTITUTE(SUBSTITUTE(A1,"-"," ",1),"-",", ")

is a lot shorter! And also results in an Excel recognized date that can be
used in calculations.

An alternative would be to use a routine that would convert "Oct-29-07
22:36:59" to a proper Excel date no matter what the Regional Settings:

=DATE(MID(A1,8,2)+1900+100*(--MID(A1,8,2)<31),
MATCH(LEFT(A1,3),{"Jan";"Feb";"Mar";"Apr";"May";"J un";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),
MID(A1,5,2))+ RIGHT(A1,8)

Ron., you're very very kind but be damned the day I choose to use
Excel in my language (italian)!... :)
Now I'm getting crazy in finding the equivalents of "SUBSTITUTE",
"DATE", "MID", "MATCH" to make your formulas work in my Excel 2007
italian.

I would the name of the person in Microsoft that decided to remove the
support of english in the formula language in not-english versions of
Excel!
Please someone teach him the meaning of the word "compatibility".

Calculate the number of days between two dates
 

On Tue, 13 Nov 2007 08:47:51 -0800, Luca Villa wrote:

=--SUBSTITUTE(SUBSTITUTE(A1,"-"," ",1),"-",", ")

is a lot shorter! And also results in an Excel recognized date that can be
used in calculations.

An alternative would be to use a routine that would convert "Oct-29-07
22:36:59" to a proper Excel date no matter what the Regional Settings:

=DATE(MID(A1,8,2)+1900+100*(--MID(A1,8,2)<31),
MATCH(LEFT(A1,3),{"Jan";"Feb";"Mar";"Apr";"May";"J un";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),
MID(A1,5,2))+ RIGHT(A1,8)

Ron., you're very very kind but be damned the day I choose to use
Excel in my language (italian)!... :)
Now I'm getting crazy in finding the equivalents of "SUBSTITUTE",
"DATE", "MID", "MATCH" to make your formulas work in my Excel 2007
italian.

I would the name of the person in Microsoft that decided to remove the
support of english in the formula language in not-english versions of
Excel!
Please someone teach him the meaning of the word "compatibility".

Ah, I did not realize that was an issue.

Of course, you must be aware that US English uses the comma as a separator, and
most non-English versions use the semicolon.

Also, this URL may be useful: http://cherbe.free.fr/traduc_fonctions_xl97.html

SUBSTITUTE SOSTITUISCI
DATE DATA
MID STRINGA.ESTRAI
MATCH CONFRONTA

--ron

Calculate the number of days between two dates
 

Ron, thanks for the table with traslations!

Now I tried "=--SOSTITUISCI(SOSTITUISCI(A1,"-"," ",1),"-",", ")" and
it worked!!
I'm impressed about it! How does it know the names of the months?

Calculate the number of days between two dates
 

On Tue, 13 Nov 2007 10:22:37 -0800, Luca Villa wrote:

Ron, thanks for the table with traslations!

Now I tried "=--SOSTITUISCI(SOSTITUISCI(A1,"-"," ",1),"-",", ")" and
it worked!!
I'm impressed about it! How does it know the names of the months?

Glad it worked.

The function doesn't need to know the names of the months, but Excel does.

Excel won't recognize:

Oct-29-07 as a date, but it will recognize:
Oct 29, 07 as a date.

The SUBSTITUTE function changes the first "-" into a <space, and the second
"-" into a <comma<space.

If you use the Tools/Formula Auditing/Evaluate Formula option, you can see the
changes "step-by-step"
--ron

Calculate the number of days between two dates
 

Thanksssss Ron! you're a real genius!!!

:D