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