Difference of date

[Atif]

1. I have 2 columns : 1. start date 2. end date, how can I formulate in the
3rd column to show the difference in days?

2. I have dates in custom format i.e dd mmm yy but still on the formula bar
it shows as d/m/yyyy. When I type in the date 2/1/05 it shows as 01 Feb 05
whereas it should show 02 Jan 05. What could be wrong here?



TIA

 

[Peo Sjoblom]

You have to change the date format under the regional setting in the control
panel, if they are set to mm/dd/yy then
for 02 Jan 05 you would need to type 01/02/05 and the format will display
correctly, if you change the date
to dd/mm/yy under regional setting you can type 2/1/05 and get it correct

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Nick Hodge]

Atif

1) Use the Datedif function with the day switch "d"

=DATEDIF(A5,A4,"d")

2) I suspect the regional settings in windows are set to US. Change these,
close and re-open Excel.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Atif]

Thanks Nick and Neo
Couldn't have thought of such prompt reply!

 

[Peo Sjoblom]

Sorry didn't see the first part, just use general format and subtract

=end-start

it is not necessary to use datedif for days

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Nick Hodge]

But I like to ;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)