Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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 HIS "Atif" wrote in message ... 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 |
#4
|
|||
|
|||
Thanks Nick and Neo
Couldn't have thought of such prompt reply! "Nick Hodge" wrote in message ... 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 HIS "Atif" wrote in message ... 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 |
#5
|
|||
|
|||
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" wrote in message ... Thanks Nick and Neo Couldn't have thought of such prompt reply! "Nick Hodge" wrote in message ... 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 HIS "Atif" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date updates from worksheet to chart & changes date to a date series! Help!! | Charts and Charting in Excel | |||
Date issue between Windows and Macintosh version | Excel Discussion (Misc queries) | |||
Calculating Date difference in 2 ways | Excel Discussion (Misc queries) | |||
Date formula | Excel Discussion (Misc queries) | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel |