L

#### Luca Villa

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?

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

L

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?

B

or use DATEDIF (undocumented function) - details at

http://www.cpearson.com/excel/datedif.aspx

best wishes

L

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

G

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.

G

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

B

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

B

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

L

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?

B

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

L

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

B

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

L

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?

R

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

R

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

L

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?

B

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

R

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

L

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

R

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

L

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

**Want to reply to this thread or ask your own question?**

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.