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