Calculate the number of days between two dates

[Luca Villa]

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]

=A2-A1 and format General
or use DATEDIF (undocumented function) - details at
http://www.cpearson.com/excel/datedif.aspx
best wishes

 

[Luca Villa]

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!

 

[Guest]

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.

 

[Guest]

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

 

[Bernard Liengme]

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

 

[Basilisk96]

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

 

[Luca Villa]

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?

 

[Bernard Liengme]

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

 

[Luca Villa]

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

 

[Bernard Liengme]

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

 

[Luca Villa]

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?

 

[Ron Rosenfeld]

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

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

 

[Ron Rosenfeld]

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

 

[Luca Villa]

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?

 

[Basilisk96]

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

 

[Ron Rosenfeld]

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

 

[Luca Villa]

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

 

[Ron Rosenfeld]

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";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),
MID(A1,5,2))+ RIGHT(A1,8)

--ron

 

[Luca Villa]

=--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";"Jun";"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".