Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Force excel to read dates as D/M/YYYY
Hello everyone,
I have a simple tracking sheet that we have people entering overtime usage in to. I am trying to summarize this info with a basic pivotchart and group it by month, day, trade, etc. Anyway, there is a column for inputing the date in to. These dates have been entered as D/M/YYYY. The trouble is, Excel reads this as M/D/ YYYY, so where 11/4/2008 is intended to be interpreted as the 11th of April, Excel is reading it as November the 4th. This results in an error whenever the "Day" exceeds 12, and so I can not group the results. Questions: 1) Is there a way to force excel to interpret a text input date in the format I desire? Such as, "read 11/4/2008 as D/M/YYYY"? 2) If no to the above, I can probably parse the text to create the decimal value Excel uses for dates (where the 5th of May 2008 = 39573 for example), except I do not know how to arrive at 39573 from 5/5/2008. Any help here would also be appreciated. I'll keep working. Thanks in advance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Force excel to read dates as D/M/YYYY
Hi
Control PanelRegional Settings Check what you have set up there, as this is where Excel takes it default settings from. -- Regards Roger Govier "S Davis" wrote in message ... Hello everyone, I have a simple tracking sheet that we have people entering overtime usage in to. I am trying to summarize this info with a basic pivotchart and group it by month, day, trade, etc. Anyway, there is a column for inputing the date in to. These dates have been entered as D/M/YYYY. The trouble is, Excel reads this as M/D/ YYYY, so where 11/4/2008 is intended to be interpreted as the 11th of April, Excel is reading it as November the 4th. This results in an error whenever the "Day" exceeds 12, and so I can not group the results. Questions: 1) Is there a way to force excel to interpret a text input date in the format I desire? Such as, "read 11/4/2008 as D/M/YYYY"? 2) If no to the above, I can probably parse the text to create the decimal value Excel uses for dates (where the 5th of May 2008 = 39573 for example), except I do not know how to arrive at 39573 from 5/5/2008. Any help here would also be appreciated. I'll keep working. Thanks in advance! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Force excel to read dates as D/M/YYYY
On May 5, 10:49 am, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote: Hi Control PanelRegional Settings Check what you have set up there, as this is where Excel takes it default settings from. -- Regards Roger Govier "S Davis" wrote in message ... Hello everyone, I have a simple tracking sheet that we have people entering overtime usage in to. I am trying to summarize this info with a basic pivotchart and group it by month, day, trade, etc. Anyway, there is a column for inputing the date in to. These dates have been entered as D/M/YYYY. The trouble is, Excel reads this as M/D/ YYYY, so where 11/4/2008 is intended to be interpreted as the 11th of April, Excel is reading it as November the 4th. This results in an error whenever the "Day" exceeds 12, and so I can not group the results. Questions: 1) Is there a way to force excel to interpret a text input date in the format I desire? Such as, "read 11/4/2008 as D/M/YYYY"? 2) If no to the above, I can probably parse the text to create the decimal value Excel uses for dates (where the 5th of May 2008 = 39573 for example), except I do not know how to arrive at 39573 from 5/5/2008. Any help here would also be appreciated. I'll keep working. Thanks in advance! Thanks. Default English settings means that a formula will be required to convert the data input already there. For those in the future needing help with this, here is my solution which works: =IF(ISERROR(DATE(YEAR(A6),MONTH(A6),DAY(A6))),DATE (RIGHT(TEXT(A6,0), 4),MID(A6,FIND("/",TEXT(A6,0))+1,(FIND("/",A6,FIND("/",TEXT(A6,0))+1)- FIND("/",TEXT(A6,0)))-1),LEFT(A6, (FIND("/",TEXT(A6,0))-1))),DATE(YEAR(A6),DAY(A6),MONTH(A6))) The inputted date resides in A6, and was input in the format D/M/YYYY, with the user manually typing in the slashes. Formula breakdown: IF(ISERROR(DATE(YEAR(A6),MONTH(A6),DAY(A6))) If: there's an error converting the input text to date (ie. if the "Day" the user input is read by excel as a month, it will error if the "Day" is over 12, eg. 13/4/2008 intended to be read as April 13th, but Errors since Excel assumes first number indicates the month number and there is no month 13) Then For Errors: DATE(RIGHT(TEXT(A6,0),4) Year: convert the input text to Text (to ensure nothing slips through) and strip the last 4 characters out for the year, MID(A6,FIND("/",TEXT(A6,0))+1,(FIND("/",A6,FIND("/",TEXT(A6,0))+1)- FIND("/",TEXT(A6,0)))-1) Month: Find the positions of the first and second "/" slashes and return the text between them LEFT(A6,(FIND("/",TEXT(A6,0))-1))) Day: Find the first "/" slash and return everything before it Else if no Errors in the original formula (ie. the "Day" is less than or equal to 12): DATE(YEAR(A6),DAY(A6),MONTH(A6))) Swap Months and Days: Return a Date formula where the Day and Month are swapped positions to match the input text. HTH -Sean |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Force excel to read dates as D/M/YYYY
Hi
Which version of Excel are you using? Try changing the settings to United States, then back again to English. With a Setting of English (United Kingdom) and location of United Kingdom, dates will be interpreted correctly (well for me on all versions of XL from 97 through 2007) If I change the settings to English (United States) and location United States, then I get the behaviour you describe. There should be no need to go through the conversion process you are adopting. -- Regards Roger Govier "S Davis" wrote in message ... On May 5, 10:49 am, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi Control PanelRegional Settings Check what you have set up there, as this is where Excel takes it default settings from. -- Regards Roger Govier "S Davis" wrote in message ... Hello everyone, I have a simple tracking sheet that we have people entering overtime usage in to. I am trying to summarize this info with a basic pivotchart and group it by month, day, trade, etc. Anyway, there is a column for inputing the date in to. These dates have been entered as D/M/YYYY. The trouble is, Excel reads this as M/D/ YYYY, so where 11/4/2008 is intended to be interpreted as the 11th of April, Excel is reading it as November the 4th. This results in an error whenever the "Day" exceeds 12, and so I can not group the results. Questions: 1) Is there a way to force excel to interpret a text input date in the format I desire? Such as, "read 11/4/2008 as D/M/YYYY"? 2) If no to the above, I can probably parse the text to create the decimal value Excel uses for dates (where the 5th of May 2008 = 39573 for example), except I do not know how to arrive at 39573 from 5/5/2008. Any help here would also be appreciated. I'll keep working. Thanks in advance! Thanks. Default English settings means that a formula will be required to convert the data input already there. For those in the future needing help with this, here is my solution which works: =IF(ISERROR(DATE(YEAR(A6),MONTH(A6),DAY(A6))),DATE (RIGHT(TEXT(A6,0), 4),MID(A6,FIND("/",TEXT(A6,0))+1,(FIND("/",A6,FIND("/",TEXT(A6,0))+1)- FIND("/",TEXT(A6,0)))-1),LEFT(A6, (FIND("/",TEXT(A6,0))-1))),DATE(YEAR(A6),DAY(A6),MONTH(A6))) The inputted date resides in A6, and was input in the format D/M/YYYY, with the user manually typing in the slashes. Formula breakdown: IF(ISERROR(DATE(YEAR(A6),MONTH(A6),DAY(A6))) If: there's an error converting the input text to date (ie. if the "Day" the user input is read by excel as a month, it will error if the "Day" is over 12, eg. 13/4/2008 intended to be read as April 13th, but Errors since Excel assumes first number indicates the month number and there is no month 13) Then For Errors: DATE(RIGHT(TEXT(A6,0),4) Year: convert the input text to Text (to ensure nothing slips through) and strip the last 4 characters out for the year, MID(A6,FIND("/",TEXT(A6,0))+1,(FIND("/",A6,FIND("/",TEXT(A6,0))+1)- FIND("/",TEXT(A6,0)))-1) Month: Find the positions of the first and second "/" slashes and return the text between them LEFT(A6,(FIND("/",TEXT(A6,0))-1))) Day: Find the first "/" slash and return everything before it Else if no Errors in the original formula (ie. the "Day" is less than or equal to 12): DATE(YEAR(A6),DAY(A6),MONTH(A6))) Swap Months and Days: Return a Date formula where the Day and Month are swapped positions to match the input text. HTH -Sean |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Force excel to read dates as D/M/YYYY
I agree - however, this sheet is being used in 6 different locations
and on more than double that for unique PC's. The conversion process ends up being necessary as I can not convert PC's default regional settings easily. This is a temporary fix anyway until I can get everything converted, paste it back in, and force M/D/YYYY date-entry from this point forward - that seems to be the easiest solution, especially given that various locations have been entering dates differently. Thanks for the help :) On May 5, 2:17 pm, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi Which version of Excel are you using? Try changing the settings to United States, then back again to English. With a Setting of English (United Kingdom) and location of United Kingdom, dates will be interpreted correctly (well for me on all versions of XL from 97 through 2007) If I change the settings to English (United States) and location United States, then I get the behaviour you describe. There should be no need to go through the conversion process you are adopting. -- Regards Roger Govier "S Davis" wrote in message ... On May 5, 10:49 am, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi Control PanelRegional Settings Check what you have set up there, as this is where Excel takes it default settings from. -- Regards Roger Govier "S Davis" wrote in message ... Hello everyone, I have a simple tracking sheet that we have people entering overtime usage in to. I am trying to summarize this info with a basic pivotchart and group it by month, day, trade, etc. Anyway, there is a column for inputing the date in to. These dates have been entered as D/M/YYYY. The trouble is, Excel reads this as M/D/ YYYY, so where 11/4/2008 is intended to be interpreted as the 11th of April, Excel is reading it as November the 4th. This results in an error whenever the "Day" exceeds 12, and so I can not group the results. Questions: 1) Is there a way to force excel to interpret a text input date in the format I desire? Such as, "read 11/4/2008 as D/M/YYYY"? 2) If no to the above, I can probably parse the text to create the decimal value Excel uses for dates (where the 5th of May 2008 = 39573 for example), except I do not know how to arrive at 39573 from 5/5/2008. Any help here would also be appreciated. I'll keep working. Thanks in advance! Thanks. Default English settings means that a formula will be required to convert the data input already there. For those in the future needing help with this, here is my solution which works: =IF(ISERROR(DATE(YEAR(A6),MONTH(A6),DAY(A6))),DATE (RIGHT(TEXT(A6,0), 4),MID(A6,FIND("/",TEXT(A6,0))+1,(FIND("/",A6,FIND("/",TEXT(A6,0))+1)- FIND("/",TEXT(A6,0)))-1),LEFT(A6, (FIND("/",TEXT(A6,0))-1))),DATE(YEAR(A6),DAY(A6),MONTH(A6))) The inputted date resides in A6, and was input in the format D/M/YYYY, with the user manually typing in the slashes. Formula breakdown: IF(ISERROR(DATE(YEAR(A6),MONTH(A6),DAY(A6))) If: there's an error converting the input text to date (ie. if the "Day" the user input is read by excel as a month, it will error if the "Day" is over 12, eg. 13/4/2008 intended to be read as April 13th, but Errors since Excel assumes first number indicates the month number and there is no month 13) Then For Errors: DATE(RIGHT(TEXT(A6,0),4) Year: convert the input text to Text (to ensure nothing slips through) and strip the last 4 characters out for the year, MID(A6,FIND("/",TEXT(A6,0))+1,(FIND("/",A6,FIND("/",TEXT(A6,0))+1)- FIND("/",TEXT(A6,0)))-1) Month: Find the positions of the first and second "/" slashes and return the text between them LEFT(A6,(FIND("/",TEXT(A6,0))-1))) Day: Find the first "/" slash and return everything before it Else if no Errors in the original formula (ie. the "Day" is less than or equal to 12): DATE(YEAR(A6),DAY(A6),MONTH(A6))) Swap Months and Days: Return a Date formula where the Day and Month are swapped positions to match the input text. HTH -Sean |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Force excel to read dates as D/M/YYYY
Hi
I understand your difficulty. One thing you could try, is to highlight the column of datesdatatext to columnsNextNextDateM/D/YFinish -- Regards Roger Govier "S Davis" wrote in message ... I agree - however, this sheet is being used in 6 different locations and on more than double that for unique PC's. The conversion process ends up being necessary as I can not convert PC's default regional settings easily. This is a temporary fix anyway until I can get everything converted, paste it back in, and force M/D/YYYY date-entry from this point forward - that seems to be the easiest solution, especially given that various locations have been entering dates differently. Thanks for the help :) On May 5, 2:17 pm, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi Which version of Excel are you using? Try changing the settings to United States, then back again to English. With a Setting of English (United Kingdom) and location of United Kingdom, dates will be interpreted correctly (well for me on all versions of XL from 97 through 2007) If I change the settings to English (United States) and location United States, then I get the behaviour you describe. There should be no need to go through the conversion process you are adopting. -- Regards Roger Govier "S Davis" wrote in message ... On May 5, 10:49 am, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi Control PanelRegional Settings Check what you have set up there, as this is where Excel takes it default settings from. -- Regards Roger Govier "S Davis" wrote in message ... Hello everyone, I have a simple tracking sheet that we have people entering overtime usage in to. I am trying to summarize this info with a basic pivotchart and group it by month, day, trade, etc. Anyway, there is a column for inputing the date in to. These dates have been entered as D/M/YYYY. The trouble is, Excel reads this as M/D/ YYYY, so where 11/4/2008 is intended to be interpreted as the 11th of April, Excel is reading it as November the 4th. This results in an error whenever the "Day" exceeds 12, and so I can not group the results. Questions: 1) Is there a way to force excel to interpret a text input date in the format I desire? Such as, "read 11/4/2008 as D/M/YYYY"? 2) If no to the above, I can probably parse the text to create the decimal value Excel uses for dates (where the 5th of May 2008 = 39573 for example), except I do not know how to arrive at 39573 from 5/5/2008. Any help here would also be appreciated. I'll keep working. Thanks in advance! Thanks. Default English settings means that a formula will be required to convert the data input already there. For those in the future needing help with this, here is my solution which works: =IF(ISERROR(DATE(YEAR(A6),MONTH(A6),DAY(A6))),DATE (RIGHT(TEXT(A6,0), 4),MID(A6,FIND("/",TEXT(A6,0))+1,(FIND("/",A6,FIND("/",TEXT(A6,0))+1)- FIND("/",TEXT(A6,0)))-1),LEFT(A6, (FIND("/",TEXT(A6,0))-1))),DATE(YEAR(A6),DAY(A6),MONTH(A6))) The inputted date resides in A6, and was input in the format D/M/YYYY, with the user manually typing in the slashes. Formula breakdown: IF(ISERROR(DATE(YEAR(A6),MONTH(A6),DAY(A6))) If: there's an error converting the input text to date (ie. if the "Day" the user input is read by excel as a month, it will error if the "Day" is over 12, eg. 13/4/2008 intended to be read as April 13th, but Errors since Excel assumes first number indicates the month number and there is no month 13) Then For Errors: DATE(RIGHT(TEXT(A6,0),4) Year: convert the input text to Text (to ensure nothing slips through) and strip the last 4 characters out for the year, MID(A6,FIND("/",TEXT(A6,0))+1,(FIND("/",A6,FIND("/",TEXT(A6,0))+1)- FIND("/",TEXT(A6,0)))-1) Month: Find the positions of the first and second "/" slashes and return the text between them LEFT(A6,(FIND("/",TEXT(A6,0))-1))) Day: Find the first "/" slash and return everything before it Else if no Errors in the original formula (ie. the "Day" is less than or equal to 12): DATE(YEAR(A6),DAY(A6),MONTH(A6))) Swap Months and Days: Return a Date formula where the Day and Month are swapped positions to match the input text. HTH -Sean |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Force excel to read dates as D/M/YYYY
On May 6, 4:23 pm, "Roger Govier" <roger@technology4unospamdotcodotuk
wrote: Hi I understand your difficulty. One thing you could try, is to highlight the column of datesdatatext to columnsNextNextDateM/D/YFinish -- Regards Roger Govier "S Davis" wrote in message ... I agree - however, this sheet is being used in 6 different locations and on more than double that for unique PC's. The conversion process ends up being necessary as I can not convert PC's default regional settings easily. This is a temporary fix anyway until I can get everything converted, paste it back in, and force M/D/YYYY date-entry from this point forward - that seems to be the easiest solution, especially given that various locations have been entering dates differently. Thanks for the help :) On May 5, 2:17 pm, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi Which version of Excel are you using? Try changing the settings to United States, then back again to English. With a Setting of English (United Kingdom) and location of United Kingdom, dates will be interpreted correctly (well for me on all versions of XL from 97 through 2007) If I change the settings to English (United States) and location United States, then I get the behaviour you describe. There should be no need to go through the conversion process you are adopting. -- Regards Roger Govier "S Davis" wrote in message ... On May 5, 10:49 am, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi Control PanelRegional Settings Check what you have set up there, as this is where Excel takes it default settings from. -- Regards Roger Govier "S Davis" wrote in message ... Hello everyone, I have a simple tracking sheet that we have people entering overtime usage in to. I am trying to summarize this info with a basic pivotchart and group it by month, day, trade, etc. Anyway, there is a column for inputing the date in to. These dates have been entered as D/M/YYYY. The trouble is, Excel reads this as M/D/ YYYY, so where 11/4/2008 is intended to be interpreted as the 11th of April, Excel is reading it as November the 4th. This results in an error whenever the "Day" exceeds 12, and so I can not group the results. Questions: 1) Is there a way to force excel to interpret a text input date in the format I desire? Such as, "read 11/4/2008 as D/M/YYYY"? 2) If no to the above, I can probably parse the text to create the decimal value Excel uses for dates (where the 5th of May 2008 = 39573 for example), except I do not know how to arrive at 39573 from 5/5/2008. Any help here would also be appreciated. I'll keep working. Thanks in advance! Thanks. Default English settings means that a formula will be required to convert the data input already there. For those in the future needing help with this, here is my solution which works: =IF(ISERROR(DATE(YEAR(A6),MONTH(A6),DAY(A6))),DATE (RIGHT(TEXT(A6,0), 4),MID(A6,FIND("/",TEXT(A6,0))+1,(FIND("/",A6,FIND("/",TEXT(A6,0))+1)- FIND("/",TEXT(A6,0)))-1),LEFT(A6, (FIND("/",TEXT(A6,0))-1))),DATE(YEAR(A6),DAY(A6),MONTH(A6))) The inputted date resides in A6, and was input in the format D/M/YYYY, with the user manually typing in the slashes. Formula breakdown: IF(ISERROR(DATE(YEAR(A6),MONTH(A6),DAY(A6))) If: there's an error converting the input text to date (ie. if the "Day" the user input is read by excel as a month, it will error if the "Day" is over 12, eg. 13/4/2008 intended to be read as April 13th, but Errors since Excel assumes first number indicates the month number and there is no month 13) Then For Errors: DATE(RIGHT(TEXT(A6,0),4) Year: convert the input text to Text (to ensure nothing slips through) and strip the last 4 characters out for the year, MID(A6,FIND("/",TEXT(A6,0))+1,(FIND("/",A6,FIND("/",TEXT(A6,0))+1)- FIND("/",TEXT(A6,0)))-1) Month: Find the positions of the first and second "/" slashes and return the text between them LEFT(A6,(FIND("/",TEXT(A6,0))-1))) Day: Find the first "/" slash and return everything before it Else if no Errors in the original formula (ie. the "Day" is less than or equal to 12): DATE(YEAR(A6),DAY(A6),MONTH(A6))) Swap Months and Days: Return a Date formula where the Day and Month are swapped positions to match the input text. HTH -Sean Thanks for the help. This is much easier than a formula for conversion, and I can run this as a macro on an entire column quite easily when I need to analyse the data. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Force read-only in auto_open macro | Excel Discussion (Misc queries) | |||
how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel | New Users to Excel | |||
In Excel, is there a way to enter dates in mm/yyyy format? | Excel Discussion (Misc queries) | |||
How to convert the dates from the YY:DD forma to MM/dd/YYYY format | Excel Worksheet Functions | |||
Excel 2003 Mis-Translates Imported DD/MM/YYYY Dates | Excel Discussion (Misc queries) |