Date Problem!!!

  • Thread starter Thread starter mandy
  • Start date Start date
M

mandy

hi all,

when i was using excel 97 to calculate no of days between two cell
(Feb 3, 2004 & 4-Nov-03), the result is correct. When i changed to us
excel xp, the result is invalid. it seems that Feb 3, 2004 can't b
recognised.

So my question is excel xp should be more advance than excel 97, right
and what i can do in order to calculate no of days between two cell
(Feb 3, 2004 & 4-Nov-03)??? i don't want to change the format of thes
dates.

Thank
 
Hi Mandy

when you right mouse click on both of the cells, go into format / cells &
choose the number tab, click on general ... what does it say under the word
sample on the right hand side?

the formatting is basically irrelavent when it comes to dates in excel - you
can (pretty much) make it look like whatever you want, what's important is
whether or not excel recognises what you think is a date as a date and this
can only be determined (AFAIK) by seeing what is stored in the cell, as per
the above method.

if you check these i'm betting that excel doesn't recognise the Feb 3, 2004
as a date and therefore won't do what you want.

how are these numbers being entered?

let me know the above and we'll see if we can come up with a solution.

Cheers
JulieD
 
Exactly what formula are you using?

With,
A1 = 11/4/03
B1 = 2/3/04

This works for me:
=DATEDIF(A1,B1,"d")

91 days!
 
Hi Mandy!

Interpretation of input of dates AFAIK is the same with Excel 97 and
Excel XP (2002).

It's a Regional Settings thing. So the difference is that your
settings have also changed.

Feb 3,2004 is interpreted as text using civilized UK settings but as a
date using US settings. I'm not sure what other Regional Settings will
also interpret Feb 3, 2004 as a date.

You can look at the Settings and change them from Start > Control
Panel > Regional and Language Options
 
-----Original Message-----
hi all,

when i was using excel 97 to calculate no of days between two cells
(Feb 3, 2004 & 4-Nov-03), the result is correct. When i changed to use
excel xp, the result is invalid. it seems that Feb 3, 2004 can't be
recognised.

So my question is excel xp should be more advance than excel 97, right?
and what i can do in order to calculate no of days between two cells
(Feb 3, 2004 & 4-Nov-03)??? i don't want to change the format of these
dates.

Thanks
Click to expand...

Hi Mandy!

If you don't want to change the 2 different date "styles",
as your entry of Feb 3, 2004 is not being recognized as a
date, you can create a custom date format for that "style".

mmm_dd,_yyyy

Also, as Julie noted, a date has an underlying numeric
value. Feb 3, 2004 has the numeric value of 38020 using
the 1900 date system. So, it's just a simple matter of
subtraction.

A1 = Feb 3, 2004
A2 = 4-NOV-03
A3 = A1-A2 = 91

Biff
 
Hi All again!

a1 = 29-Mar-04
Format : Custom d-mmm-yy (when i click on it, it shows 29/3/2004 )

b1 = Jun 28, 2004 (when i click on it, it shows 'Jun 28, 2004)
Format : Custom d-mmm-yy or General (result can be obtained by usin
either format)

c1=DATEDIF(a1,b1,"d')

The problem is why i can successfully obtain the result when usin
excel 97, but failed when using excel xp?

for b1, i tried changing the format to mmm_dd,_yyyy, but still failed.

I have checked the regional & language option, it's Chinese (Hong Kong
SAR)

Actually, the excel is created by my colleage, i will need to view he
file quite often. she is using excel 97 , i was using excel 97 befor
and viewing the file without any problem . Once i start using excel xp
i am not able to view b1.

Please help ! thanks
 
Hi Mandy!

Try changing to US Regional Setting. As I said before, I really don't
think that it's an Excel 97 v. Excel XP thing.
 
Hi Norman Harker,

I have just changed the regional and language options from HK to Unite
States, all the value can be viewed!!! Thank you !

And thanks for all the help here!!

Mand
 
Hi Mandy!

That's OK! Pleased to help out. It certainly is a nuisance that
difference language options treat that particular input differently.

For further reference, the following dates inputs are all unequivocal
and accepted by all English language versions of Excel:

12 January 2002
12-January-2002
12/January/2002
12 Jan 2002
12-Jan-2002
12/Jan/2002
2002-01-12
2002/01/12
2002/1/12

Methods 3,4 and 5 are the most commonly selected. They are quick to
type and take up the least width of cell.

Method 7 is ISO8601:2000 approved (separated) form and has the even
greater advantage that it does not "demand" an English language date.
I believe it is a form that is recognized by all Regional settings but
I haven't checked that out for all countries and all Excel Versions
(OK for Versions down to Excel 97). For other forms the short or long
month names need translating to the appropriate language. Methods 8
and 9 are non-compliant ISO ordered and I would ask, "Why use these
when the real McCoy is available?"
 
Back
Top