period of time between 2 dates calculated automatically

  • Thread starter Thread starter jvoortman
  • Start date Start date
J

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.
 
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
Click to expand...
 
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!
Click to expand...
 
perhaps I should have mentioned that the dates are
expressed like: 08-26-2003
maybe this is why the easy method shows #value!
Click to expand...

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

jvoortman` said:
03-28-2003 08-26-2003 #VALUE!
-----Original Message-----
Have you subtracted the earlier date from the later one?
Click to expand...
Excel can't display a negative date.
Click to expand...
 
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.)
Click to expand...
 
I think it expects the date in the format specified in your Windows Regional Settings.
 
Back
Top