Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Uk Date Format to US Date Format
Hello, I have a sheet that contains dates in column (W) formatted per the UK
26/01/2009 12:45:37 In order to get the month and year, do I need to reformat them to US or is there another way to get the month and year correctly? If so how do I do this? Thank you |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Uk Date Format to US Date Format
Assuming it is truly just the format that's causing the display (and not text
inputted as a date) you can just use the MONTH and YEAR functions. Example: =MONTH(SerialDate) See XL help file for further detail. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "TDMP" wrote: Hello, I have a sheet that contains dates in column (W) formatted per the UK 26/01/2009 12:45:37 In order to get the month and year, do I need to reformat them to US or is there another way to get the month and year correctly? If so how do I do this? Thank you |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Uk Date Format to US Date Format
It depends on your requirements.
You can simply change the cell format to *display* only the month/year or you can use separate cells to get the month/year. If you simply change the cells format to *display* only the month/year the TRUE underlying value of the cell will *still* be 26/01/2009 12:45:37. So, it depends on what you really want to do. -- Biff Microsoft Excel MVP "TDMP" wrote in message ... Hello, I have a sheet that contains dates in column (W) formatted per the UK 26/01/2009 12:45:37 In order to get the month and year, do I need to reformat them to US or is there another way to get the month and year correctly? If so how do I do this? Thank you |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Uk Date Format to US Date Format
Hi Luke that is the issue when using the Month function, I get a value error
returned ..I assume its because excel is not understanding the month being 26/1/2009 ....I assume excel needs it to read 1/26/2009...does this make sense? FYI this data is from a dump off a server in the UK so the dates wil come back in UK format... "Luke M" wrote: Assuming it is truly just the format that's causing the display (and not text inputted as a date) you can just use the MONTH and YEAR functions. Example: =MONTH(SerialDate) See XL help file for further detail. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "TDMP" wrote: Hello, I have a sheet that contains dates in column (W) formatted per the UK 26/01/2009 12:45:37 In order to get the month and year, do I need to reformat them to US or is there another way to get the month and year correctly? If so how do I do this? Thank you |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Uk Date Format to US Date Format
Formatting only changes what you see and not the underlying value.
Format your sample date time as general for a moment and look in the formula bar and you should see 39839.5316782407 which is the date time you posted as a number which is how Excel stores it with 39839 being the date and the decimal part being the time so UK/US formatting won't effect the outcome of Day(a1) Month(a1) Year (a1) If your getting a value error then your dates probably aren't dates they are likely to be text that looks like a date/time. where do these dates come from? Mike "TDMP" wrote: Hi Luke that is the issue when using the Month function, I get a value error returned ..I assume its because excel is not understanding the month being 26/1/2009 ....I assume excel needs it to read 1/26/2009...does this make sense? FYI this data is from a dump off a server in the UK so the dates wil come back in UK format... "Luke M" wrote: Assuming it is truly just the format that's causing the display (and not text inputted as a date) you can just use the MONTH and YEAR functions. Example: =MONTH(SerialDate) See XL help file for further detail. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "TDMP" wrote: Hello, I have a sheet that contains dates in column (W) formatted per the UK 26/01/2009 12:45:37 In order to get the month and year, do I need to reformat them to US or is there another way to get the month and year correctly? If so how do I do this? Thank you |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Uk Date Format to US Date Format
It's because all dates where the days are greater than the 12th will come
out as text, there are a couple of ways to solve this, you can parse them out using a formula or the easier way, make sure the column to the right of the imports is empty, then select the column, do datatext to columns, select delimited and click next, select space as delimiter, click next, select the date column and under column data format in step 3 select date and DMY (not MDY), then if you need the times click finish. That will give you the time in one column and the dates in one, in a third column just add them like in A1+B1 and copy down, then custom format as date and time. If you don't need them, select the time column in step 3 and select do not import and click finish -- Regards, Peo Sjoblom "TDMP" wrote in message ... Hi Luke that is the issue when using the Month function, I get a value error returned ..I assume its because excel is not understanding the month being 26/1/2009 ....I assume excel needs it to read 1/26/2009...does this make sense? FYI this data is from a dump off a server in the UK so the dates wil come back in UK format... "Luke M" wrote: Assuming it is truly just the format that's causing the display (and not text inputted as a date) you can just use the MONTH and YEAR functions. Example: =MONTH(SerialDate) See XL help file for further detail. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "TDMP" wrote: Hello, I have a sheet that contains dates in column (W) formatted per the UK 26/01/2009 12:45:37 In order to get the month and year, do I need to reformat them to US or is there another way to get the month and year correctly? If so how do I do this? Thank you |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Uk Date Format to US Date Format
They come from a reporting server that sits in the UK the field is called
submit date..I have had this issue before with UK dates and trying to get the month, year, and week of month from the field...I looked at the format and it is custom m/d/yyyy h:mm..if i try and change it to an actual date and time excel wont let me "Mike H" wrote: Formatting only changes what you see and not the underlying value. Format your sample date time as general for a moment and look in the formula bar and you should see 39839.5316782407 which is the date time you posted as a number which is how Excel stores it with 39839 being the date and the decimal part being the time so UK/US formatting won't effect the outcome of Day(a1) Month(a1) Year (a1) If your getting a value error then your dates probably aren't dates they are likely to be text that looks like a date/time. where do these dates come from? Mike "TDMP" wrote: Hi Luke that is the issue when using the Month function, I get a value error returned ..I assume its because excel is not understanding the month being 26/1/2009 ....I assume excel needs it to read 1/26/2009...does this make sense? FYI this data is from a dump off a server in the UK so the dates wil come back in UK format... "Luke M" wrote: Assuming it is truly just the format that's causing the display (and not text inputted as a date) you can just use the MONTH and YEAR functions. Example: =MONTH(SerialDate) See XL help file for further detail. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "TDMP" wrote: Hello, I have a sheet that contains dates in column (W) formatted per the UK 26/01/2009 12:45:37 In order to get the month and year, do I need to reformat them to US or is there another way to get the month and year correctly? If so how do I do this? Thank you |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Uk Date Format to US Date Format
If Excel has treated your data as text, that's probably because you've got
the wrong settings in your Windows Regional Options (in Control Panel, not in Excel). Excel cell formattting governs how a date is *displayed* if it has been stored as a number (not text), but the control of how an *input* is interpreted is through Windows Regional Options. -- David Biddulph TDMP wrote: Hi Luke that is the issue when using the Month function, I get a value error returned ..I assume its because excel is not understanding the month being 26/1/2009 ....I assume excel needs it to read 1/26/2009...does this make sense? FYI this data is from a dump off a server in the UK so the dates wil come back in UK format... "Luke M" wrote: Assuming it is truly just the format that's causing the display (and not text inputted as a date) you can just use the MONTH and YEAR functions. Example: =MONTH(SerialDate) See XL help file for further detail. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "TDMP" wrote: Hello, I have a sheet that contains dates in column (W) formatted per the UK 26/01/2009 12:45:37 In order to get the month and year, do I need to reformat them to US or is there another way to get the month and year correctly? If so how do I do this? Thank you |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Uk Date Format to US Date Format
To convert that to excel date time try one of the below
'Using formulas =DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))+MID(A1,1 2,8) 'Using Data Text to columns wizard 1. Select the range/column needs to be changed. From menu DataText to Columns will populate the 'Convert Text to Columns Wizard' By default the selection is 'Delimited'. Keep the selection and hit 'Next'. From the Step2 of the Wizard from the options select Space and hit Next. Now you have the date and time in separate columns 2 .You can use the 'Convert Text to Columns Wizard to convert the dates --Select the range of dates which needs to be corrected. --From menu Data'Text to Columns' will populate the 'Convert Text to Columns Wizard'. --Hit NextNext will take you to Step 3 of 3 of the Wizard. --From 'Column Data format' select 'Date' and select the date format in which your data is ('DMY' in your case). --Hit Finish. MSExcel will now convert the dates to the default date format of your computer. 3. Now you can add the two columns to combine date and time =A1+B1 If this post helps click Yes --------------- Jacob Skaria "TDMP" wrote: They come from a reporting server that sits in the UK the field is called submit date..I have had this issue before with UK dates and trying to get the month, year, and week of month from the field...I looked at the format and it is custom m/d/yyyy h:mm..if i try and change it to an actual date and time excel wont let me "Mike H" wrote: Formatting only changes what you see and not the underlying value. Format your sample date time as general for a moment and look in the formula bar and you should see 39839.5316782407 which is the date time you posted as a number which is how Excel stores it with 39839 being the date and the decimal part being the time so UK/US formatting won't effect the outcome of Day(a1) Month(a1) Year (a1) If your getting a value error then your dates probably aren't dates they are likely to be text that looks like a date/time. where do these dates come from? Mike "TDMP" wrote: Hi Luke that is the issue when using the Month function, I get a value error returned ..I assume its because excel is not understanding the month being 26/1/2009 ....I assume excel needs it to read 1/26/2009...does this make sense? FYI this data is from a dump off a server in the UK so the dates wil come back in UK format... "Luke M" wrote: Assuming it is truly just the format that's causing the display (and not text inputted as a date) you can just use the MONTH and YEAR functions. Example: =MONTH(SerialDate) See XL help file for further detail. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "TDMP" wrote: Hello, I have a sheet that contains dates in column (W) formatted per the UK 26/01/2009 12:45:37 In order to get the month and year, do I need to reformat them to US or is there another way to get the month and year correctly? If so how do I do this? Thank you |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Uk Date Format to US Date Format
Peo, you are a genius! Thanks so much! I had the same issue,.
On Tuesday, November 3, 2009 3:12:19 PM UTC-5, Peo Sjoblom wrote: It's because all dates where the days are greater than the 12th will come out as text, there are a couple of ways to solve this, you can parse them out using a formula or the easier way, make sure the column to the right of the imports is empty, then select the column, do datatext to columns, select delimited and click next, select space as delimiter, click next, select the date column and under column data format in step 3 select date and DMY (not MDY), then if you need the times click finish. That will give you the time in one column and the dates in one, in a third column just add them like in A1+B1 and copy down, then custom format as date and time. If you don't need them, select the time column in step 3 and select do not import and click finish -- Regards, Peo Sjoblom "TDMP" wrote in message ... Hi Luke that is the issue when using the Month function, I get a value error returned ..I assume its because excel is not understanding the month being 26/1/2009 ....I assume excel needs it to read 1/26/2009...does this make sense? FYI this data is from a dump off a server in the UK so the dates wil come back in UK format... "Luke M" wrote: Assuming it is truly just the format that's causing the display (and not text inputted as a date) you can just use the MONTH and YEAR functions. Example: =MONTH(SerialDate) See XL help file for further detail. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "TDMP" wrote: Hello, I have a sheet that contains dates in column (W) formatted per the UK 26/01/2009 12:45:37 In order to get the month and year, do I need to reformat them to US or is there another way to get the month and year correctly? If so how do I do this? Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
Convert european foreign date format to US date format | Excel Worksheet Functions | |||
convert serial date format to normal date format | Excel Discussion (Misc queries) | |||
Convert date + time text format to date format | Excel Worksheet Functions | |||
Excel 2000 date format cannot be set to Australian date format | Excel Discussion (Misc queries) |