period of time between 2 dates calculated automatically

[jvoortman]

in cell A1 I have the beginning date and in cell B1 the
finishing date. I want to know if there is an easy way to
calculate the number of days.

 

[Myrna Larson]

Very easy <vbg>

=B1-A1

 

[jvoortman]

perhaps I should have mentioned that the dates are
expressed like: 08-26-2003
maybe this is why the easy method shows #value!

-----Original Message-----
Very easy <vbg>

=B1-A1

 

[Myrna Larson]

Have you subtracted the earlier date from the later one? Excel can't display a negative date.

perhaps I should have mentioned that the dates are
expressed like: 08-26-2003
maybe this is why the easy method shows #value!

 

[Ron Rosenfeld]

perhaps I should have mentioned that the dates are
expressed like: 08-26-2003
maybe this is why the easy method shows #value!

Probably the date is text, rather than an XL recognized time.

You can try one of the following to convert to a number:

=VALUE(A1)
=VALUE(SUBSTITUTE(A1,"-","X")) where "X" is whatever the date separator is in
your international settings.

=YEAR(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))


--ron

 

[Dave Peterson]

Another way to convert those Text values to Date values:

Select the range
Edit|Replace
replace -
with /
(replace all)

If your short date in windows regional settings is set in m,d,y order, it should
work.

(But do it against a copy of your workbook, just in case.)

03-28-2003 08-26-2003 #VALUE!

-----Original Message-----
Have you subtracted the earlier date from the later one?

Excel can't display a negative date.

 

[jvoortman]

doesn't work, still get #value!in cell c1. Iam using excel
in office 2000 (if that makes any difference)

-----Original Message-----
Another way to convert those Text values to Date values:

Select the range
Edit|Replace
replace -
with /
(replace all)

If your short date in windows regional settings is set in m,d,y order, it should
work.

(But do it against a copy of your workbook, just in case.)

 

[Myrna Larson]

I think it expects the date in the format specified in your Windows Regional Settings.