Difference of date

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

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)



"Atif" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

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
[email protected]HIS


"Atif" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

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

"Nick Hodge" <[email protected]> wrote in message
news:%[email protected]...
> 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
> [email protected]HIS
>
>
> "Atif" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>>
>
>

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)



"Atif" <[email protected]> wrote in message
news:%[email protected]...
> Thanks Nick and Neo
> Couldn't have thought of such prompt reply!
>
> "Nick Hodge" <[email protected]> wrote in message
> news:%[email protected]...
>> 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
>> [email protected]HIS
>>
>>
>> "Atif" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>>
>>>
>>>
>>
>>
>
>

But I like to ;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]HIS


"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> 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)
>
>
>
> "Atif" <[email protected]> wrote in message
> news:%[email protected]...
>> Thanks Nick and Neo
>> Couldn't have thought of such prompt reply!
>>
>> "Nick Hodge" <[email protected]> wrote in message
>> news:%[email protected]...
>>> 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
>>> [email protected]HIS
>>>
>>>
>>> "Atif" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>