Get month and year from date cells Excel

Question

I want to isolate month and year from a date column  but this column has two formats    08 May 2011 00 59 and 08-05-2011    If I want to have a new column to isolate month and year from this column like  05-2011  how should I do that

User · Answer

Try this formula  it will return value from A1 as is if it s not a date     TEXT A1  mm-yyyy     Or this formula  it s more strict  it will return  VALUE error if A1 is not date     TEXT MONTH A1   00   amp  -  amp YEAR A1

User · Answer

You could right click on those cells  go to format  select custom  then type mm yyyy

User · Answer

I had a requirement to provide a report showing details by month where the date field was formatted as date  amp  time  I simply changed the formatting of the date column to  General  and then used the following formula in a new column     CONCATENATE YEAR C2  MONTH C2

User · Answer

Please try something like      IF LEN C1  gt 10 VALUE LEFT C1 FIND     C1 8    IF ISTEXT C1  DATE RIGHT C1 4  MID C1 4 2  LEFT C1 2   C1       You seem to have three main possible scenarios      Space-separated date with time as text   eg as A1 below    Hyphen-separated date as text  eg as A2 below  Formatted date index  as A4 and A5 below      ColumnA below is formatted General and ColumnB as Date  my default setting   ColumnC also as date but with custom formatting to suit the appearances mentioned in your question  A clue as to whether or not text format is the left or right alignment of the cells    contents   I am suggesting separate treatment for each of the above three main cases  so use  IF to differentiate them     Case  1  This is longer than any of the others  so can be distinguished as having a length greater than say 10 characters  with  LEN  In this case we want all but the last six characters but for added flexibility  for instance  in case the time element included seconds  I have chosen to count from the left rather than from the right  The problem then is that the month names may vary in length  so I have chosen to look for the space that immediately follows the year to indicate the limit for the relevant number of characters     This with  FIND which looks for a space       in C1  starting with the eighth character within C1 counting from the left  on the assumption that for this case days will be expressed as two characters and months as three or more     Since  LEFT is a string function it returns a string  but this can be converted to a value with VALUE     So     VALUE LEFT C1 FIND     C1 8       returns 40671 in this example     in Excel   s 1900 date system the date serial number for May 5  2011   Case  2  If the length of C1 is not greater than 10 characters  we still need to distinguish between a text entry or a value entry which I have chosen to do with  ISTEXT and  where the if condition is TRUE  as for C2  apply  DATE which takes three parameters  here provided by      RIGHT C2 4      Takes the last four characters of C2  hence 2011 in this example      MID C2 4 2     Starting at the fourth character  takes the next two characters of C2  hence 05 in this example  representing May      LEFT C2 2       Takes the first two characters of C2  hence 08 in this example  representing the 8th day of the month   Date is not a text function so does not need to be wrapped in  VALUE     Taken together     DATE RIGHT C2 4  MID C2 4 2  LEFT C2 2      also returns 40671 in this example  but from different input from Case  1     Case  3  Is simple because already a date serial number  so just     C2     is sufficient       Put the above together to cover all three cases in a single formula      IF LEN C1  gt 10 VALUE LEFT C1 FIND     C1 8    IF ISTEXT C1  DATE RIGHT C1 4  MID C1 4 2  LEFT C1 2   C1       as applied in ColumnF  formatted to suit OP  or in General format  to show values are integers  in ColumnH

[excel] Get month and year from date cells Excel

Examples related to excel