Uk Date Format to US Date Format

[TDMP]

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

 

[Luke M]

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.

 

[T. Valko]

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.

 

[TDMP]

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...

 

[Mike H]

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

 

[Peo Sjoblom]

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 data>text 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]

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

 

[David Biddulph]

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.

 

[Jacob Skaria]

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,12,8)


'Using Data Text to columns wizard
1. Select the range/column needs to be changed. From menu Data>Text 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 Next>Next 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

 

[aigerim.kikimova]

Peo, you are a genius! Thanks so much! I had the same issue,.