| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
date out of control
Could anyone tell me why I can not control the date format in excel 2002
please The problem I have is that I have dates created as 20010926 20010927 20010928 20011001 20011002 etc and I want to convert them to dates in the format 26/09/2001 27/09/2001 28/09/2001 01/10/2001 02/10/2001 etc I have the following code For ind = 2 To lastRow yy = Left(Cells(ind, "a").Value, 4) mm = Mid(Cells(ind, "a").Value, 5, 2) dd = Right(Cells(ind, "a").Value, 2) Cells(ind, "a").Value = dd & "/" & mm & "/" & yy Next ind When I run this the result is 26/09/2001 27/09/2001 28/09/2001 10/01/2001 10/02/2001 ie, all days <13 are rightjustified and formatted mm/dd/yyyy and all other days are left justified and formatted dd/mm/yyyy Why wont excel do as I tell it? - I want all my dates formatted as dd/mm/yyyy and I dont care whether the are left or right justified. Thanks Chris |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date out of control
Try DateSerial
Cells(ind, "a").Value = DateSerial(yy, mm, dd) -- Rob van Gelder - http://www.vangelder.co.nz/excel "inquirer" wrote in message ... Could anyone tell me why I can not control the date format in excel 2002 please The problem I have is that I have dates created as 20010926 20010927 20010928 20011001 20011002 etc and I want to convert them to dates in the format 26/09/2001 27/09/2001 28/09/2001 01/10/2001 02/10/2001 etc I have the following code For ind = 2 To lastRow yy = Left(Cells(ind, "a").Value, 4) mm = Mid(Cells(ind, "a").Value, 5, 2) dd = Right(Cells(ind, "a").Value, 2) Cells(ind, "a").Value = dd & "/" & mm & "/" & yy Next ind When I run this the result is 26/09/2001 27/09/2001 28/09/2001 10/01/2001 10/02/2001 ie, all days <13 are rightjustified and formatted mm/dd/yyyy and all other days are left justified and formatted dd/mm/yyyy Why wont excel do as I tell it? - I want all my dates formatted as dd/mm/yyyy and I dont care whether the are left or right justified. Thanks Chris |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date out of control
Thanks for your swift reply Rob, that fixed it. I'd still like to know why
what I had gave the "funny " result Chris "Rob van Gelder" wrote in message ... Try DateSerial Cells(ind, "a").Value = DateSerial(yy, mm, dd) -- Rob van Gelder - http://www.vangelder.co.nz/excel "inquirer" wrote in message ... Could anyone tell me why I can not control the date format in excel 2002 please The problem I have is that I have dates created as 20010926 20010927 20010928 20011001 20011002 etc and I want to convert them to dates in the format 26/09/2001 27/09/2001 28/09/2001 01/10/2001 02/10/2001 etc I have the following code For ind = 2 To lastRow yy = Left(Cells(ind, "a").Value, 4) mm = Mid(Cells(ind, "a").Value, 5, 2) dd = Right(Cells(ind, "a").Value, 2) Cells(ind, "a").Value = dd & "/" & mm & "/" & yy Next ind When I run this the result is 26/09/2001 27/09/2001 28/09/2001 10/01/2001 10/02/2001 ie, all days <13 are rightjustified and formatted mm/dd/yyyy and all other days are left justified and formatted dd/mm/yyyy Why wont excel do as I tell it? - I want all my dates formatted as dd/mm/yyyy and I dont care whether the are left or right justified. Thanks Chris |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date out of control
Excel and VBA are in love with the American date format.
A couple of examples. 'both examples assume user's regional settings are dd/mm/yyyy Sub TestExcel() Dim i As Long, dtm As Date, str As String Columns(1).NumberFormat = "dd-mmm-yyyy" dtm = "1-Jan-2004" With Range("A1") .Value = "'dd/mm/yyyy" For i = 0 To 365 - 1 str = Format(dtm + i, "dd/mm/yyyy") .Offset(i + 1).Value = str Next End With With Range("B1") .Value = "'dd/mm/yyyy" For i = 0 To 365 - 1 str = Format(dtm + i, "mm/dd/yyyy") .Offset(i + 1).Value = str Next End With End Sub Sub TestVBA() Dim dtm As Date, str As String 'VBA looks at your regional settings str = "01/12/2001" dtm = str MsgBox Format(dtm, "dd-mmm-yyyy") 'But if the regional settings don't match to this date it tries US style str = "01/13/2001" dtm = str MsgBox Format(dtm, "dd-mmm-yyyy") End Sub -- Rob van Gelder - http://www.vangelder.co.nz/excel "inquirer" wrote in message ... Thanks for your swift reply Rob, that fixed it. I'd still like to know why what I had gave the "funny " result Chris "Rob van Gelder" wrote in message ... Try DateSerial Cells(ind, "a").Value = DateSerial(yy, mm, dd) -- Rob van Gelder - http://www.vangelder.co.nz/excel "inquirer" wrote in message ... Could anyone tell me why I can not control the date format in excel 2002 please The problem I have is that I have dates created as 20010926 20010927 20010928 20011001 20011002 etc and I want to convert them to dates in the format 26/09/2001 27/09/2001 28/09/2001 01/10/2001 02/10/2001 etc I have the following code For ind = 2 To lastRow yy = Left(Cells(ind, "a").Value, 4) mm = Mid(Cells(ind, "a").Value, 5, 2) dd = Right(Cells(ind, "a").Value, 2) Cells(ind, "a").Value = dd & "/" & mm & "/" & yy Next ind When I run this the result is 26/09/2001 27/09/2001 28/09/2001 10/01/2001 10/02/2001 ie, all days <13 are rightjustified and formatted mm/dd/yyyy and all other days are left justified and formatted dd/mm/yyyy Why wont excel do as I tell it? - I want all my dates formatted as dd/mm/yyyy and I dont care whether the are left or right justified. Thanks Chris |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date out of control
Thanks Rob - I thought a regional setting would take precedence over
everything otherwise why bother having one. It probably takes precedence over everything so long as it isn't American Chris "Rob van Gelder" wrote in message ... Excel and VBA are in love with the American date format. A couple of examples. 'both examples assume user's regional settings are dd/mm/yyyy Sub TestExcel() Dim i As Long, dtm As Date, str As String Columns(1).NumberFormat = "dd-mmm-yyyy" dtm = "1-Jan-2004" With Range("A1") .Value = "'dd/mm/yyyy" For i = 0 To 365 - 1 str = Format(dtm + i, "dd/mm/yyyy") .Offset(i + 1).Value = str Next End With With Range("B1") .Value = "'dd/mm/yyyy" For i = 0 To 365 - 1 str = Format(dtm + i, "mm/dd/yyyy") .Offset(i + 1).Value = str Next End With End Sub Sub TestVBA() Dim dtm As Date, str As String 'VBA looks at your regional settings str = "01/12/2001" dtm = str MsgBox Format(dtm, "dd-mmm-yyyy") 'But if the regional settings don't match to this date it tries US style str = "01/13/2001" dtm = str MsgBox Format(dtm, "dd-mmm-yyyy") End Sub -- Rob van Gelder - http://www.vangelder.co.nz/excel "inquirer" wrote in message ... Thanks for your swift reply Rob, that fixed it. I'd still like to know why what I had gave the "funny " result Chris "Rob van Gelder" wrote in message ... Try DateSerial Cells(ind, "a").Value = DateSerial(yy, mm, dd) -- Rob van Gelder - http://www.vangelder.co.nz/excel "inquirer" wrote in message ... Could anyone tell me why I can not control the date format in excel 2002 please The problem I have is that I have dates created as 20010926 20010927 20010928 20011001 20011002 etc and I want to convert them to dates in the format 26/09/2001 27/09/2001 28/09/2001 01/10/2001 02/10/2001 etc I have the following code For ind = 2 To lastRow yy = Left(Cells(ind, "a").Value, 4) mm = Mid(Cells(ind, "a").Value, 5, 2) dd = Right(Cells(ind, "a").Value, 2) Cells(ind, "a").Value = dd & "/" & mm & "/" & yy Next ind When I run this the result is 26/09/2001 27/09/2001 28/09/2001 10/01/2001 10/02/2001 ie, all days <13 are rightjustified and formatted mm/dd/yyyy and all other days are left justified and formatted dd/mm/yyyy Why wont excel do as I tell it? - I want all my dates formatted as dd/mm/yyyy and I dont care whether the are left or right justified. Thanks Chris |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date out of control
Maybe so..
avoid confusion by avoiding the value property and assigning a dateserial to Value2 AND formatting yourself Sub Dates() Dim i% Columns(1).NumberFormat = _ " ""Year:"" yyyy ""Day:"" dd ""Month:"" mm \(dddd \)" For i = 1 To 365 Cells(i, 1).Value2 = DateSerial(2003, 12, 31) + i Next End Sub see.. no problems. keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "inquirer" wrote: Thanks Rob - I thought a regional setting would take precedence over everything otherwise why bother having one. It probably takes precedence over everything so long as it isn't American Chris "Rob van Gelder" wrote in message ... Excel and VBA are in love with the American date format. A couple of examples. 'both examples assume user's regional settings are dd/mm/yyyy Sub TestExcel() Dim i As Long, dtm As Date, str As String Columns(1).NumberFormat = "dd-mmm-yyyy" dtm = "1-Jan-2004" With Range("A1") .Value = "'dd/mm/yyyy" For i = 0 To 365 - 1 str = Format(dtm + i, "dd/mm/yyyy") .Offset(i + 1).Value = str Next End With With Range("B1") .Value = "'dd/mm/yyyy" For i = 0 To 365 - 1 str = Format(dtm + i, "mm/dd/yyyy") .Offset(i + 1).Value = str Next End With End Sub Sub TestVBA() Dim dtm As Date, str As String 'VBA looks at your regional settings str = "01/12/2001" dtm = str MsgBox Format(dtm, "dd-mmm-yyyy") 'But if the regional settings don't match to this date it tries US style str = "01/13/2001" dtm = str MsgBox Format(dtm, "dd-mmm-yyyy") End Sub -- Rob van Gelder - http://www.vangelder.co.nz/excel "inquirer" wrote in message ... Thanks for your swift reply Rob, that fixed it. I'd still like to know why what I had gave the "funny " result Chris "Rob van Gelder" wrote in message ... Try DateSerial Cells(ind, "a").Value = DateSerial(yy, mm, dd) -- Rob van Gelder - http://www.vangelder.co.nz/excel "inquirer" wrote in message ... Could anyone tell me why I can not control the date format in excel 2002 please The problem I have is that I have dates created as 20010926 20010927 20010928 20011001 20011002 etc and I want to convert them to dates in the format 26/09/2001 27/09/2001 28/09/2001 01/10/2001 02/10/2001 etc I have the following code For ind = 2 To lastRow yy = Left(Cells(ind, "a").Value, 4) mm = Mid(Cells(ind, "a").Value, 5, 2) dd = Right(Cells(ind, "a").Value, 2) Cells(ind, "a").Value = dd & "/" & mm & "/" & yy Next ind When I run this the result is 26/09/2001 27/09/2001 28/09/2001 10/01/2001 10/02/2001 ie, all days <13 are rightjustified and formatted mm/dd/yyyy and all other days are left justified and formatted dd/mm/yyyy Why wont excel do as I tell it? - I want all my dates formatted as dd/mm/yyyy and I dont care whether the are left or right justified. Thanks Chris |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
date out of control
So if you wrote your code to work with your regional settings and then sent
your worksheet to another locality, you would want your code to fail because their regional settings were different? Sounds like a plan. -- Regards, Tom Ogilvy "inquirer" wrote in message ... Thanks Rob - I thought a regional setting would take precedence over everything otherwise why bother having one. It probably takes precedence over everything so long as it isn't American Chris "Rob van Gelder" wrote in message ... Excel and VBA are in love with the American date format. A couple of examples. 'both examples assume user's regional settings are dd/mm/yyyy Sub TestExcel() Dim i As Long, dtm As Date, str As String Columns(1).NumberFormat = "dd-mmm-yyyy" dtm = "1-Jan-2004" With Range("A1") .Value = "'dd/mm/yyyy" For i = 0 To 365 - 1 str = Format(dtm + i, "dd/mm/yyyy") .Offset(i + 1).Value = str Next End With With Range("B1") .Value = "'dd/mm/yyyy" For i = 0 To 365 - 1 str = Format(dtm + i, "mm/dd/yyyy") .Offset(i + 1).Value = str Next End With End Sub Sub TestVBA() Dim dtm As Date, str As String 'VBA looks at your regional settings str = "01/12/2001" dtm = str MsgBox Format(dtm, "dd-mmm-yyyy") 'But if the regional settings don't match to this date it tries US style str = "01/13/2001" dtm = str MsgBox Format(dtm, "dd-mmm-yyyy") End Sub -- Rob van Gelder - http://www.vangelder.co.nz/excel "inquirer" wrote in message ... Thanks for your swift reply Rob, that fixed it. I'd still like to know why what I had gave the "funny " result Chris "Rob van Gelder" wrote in message ... Try DateSerial Cells(ind, "a").Value = DateSerial(yy, mm, dd) -- Rob van Gelder - http://www.vangelder.co.nz/excel "inquirer" wrote in message ... Could anyone tell me why I can not control the date format in excel 2002 please The problem I have is that I have dates created as 20010926 20010927 20010928 20011001 20011002 etc and I want to convert them to dates in the format 26/09/2001 27/09/2001 28/09/2001 01/10/2001 02/10/2001 etc I have the following code For ind = 2 To lastRow yy = Left(Cells(ind, "a").Value, 4) mm = Mid(Cells(ind, "a").Value, 5, 2) dd = Right(Cells(ind, "a").Value, 2) Cells(ind, "a").Value = dd & "/" & mm & "/" & yy Next ind When I run this the result is 26/09/2001 27/09/2001 28/09/2001 10/01/2001 10/02/2001 ie, all days <13 are rightjustified and formatted mm/dd/yyyy and all other days are left justified and formatted dd/mm/yyyy Why wont excel do as I tell it? - I want all my dates formatted as dd/mm/yyyy and I dont care whether the are left or right justified. Thanks Chris |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date picker control | Excel Discussion (Misc queries) | |||
| Required Date picker control | Excel Discussion (Misc queries) | |||
| Date and Time Picker Control | Excel Discussion (Misc queries) | |||
| Date Control using Spinner | Excel Programming | |||
| calendar/date control | Excel Programming | |||
Linear Mode
