0

I have a string of this particular format:

DD/MMM/YY HH:MM AM/PM

e.g.

03/Nov/14 9:00 AM

I could not find figure out how to make Calc recognize it as a date so I had to do the following for a date string in B2:

=DATEVALUE(
    CONCATENATE(
        2000+MID(B2,8,2),
        "-",
        IF(MID(B2,4,3)="Jan",1,
            IF(MID(B2,4,3)="Feb",2,
                IF(MID(B2,4,3)="Mar",3,
                    IF(MID(B2,4,3)="Apr",4,
                        IF(MID(B2,4,3)="May",5,
                            IF(MID(B2,4,3)="Jun",6,
                                IF(MID(B2,4,3)="Jul",7,
                                    IF(MID(B2,4,3)="Aug",8,
                                        IF(MID(B2,4,3)="Sep",9,
                                            IF(MID(B2,4,3)="Oct",10,
                                                IF(MID(B2,4,3)="Nov",11,
                                                    IF(MID(B2,4,3)="Dec",12,""
        )))))))))))),
        "-",
        MID(B2,1,2)
    )
)
+
TIMEVALUE(
    IF(MID(B2,8,1)=" ",RIGHT(B2,7),RIGHT(B2,8))
)

or on one line:

=DATEVALUE(CONCATENATE(2000+MID(B2,8,2),"-",IF(MID(B2,4,3)="Jan",1,IF(MID(B2,4,3)="Feb",2,IF(MID(B2,4,3)="Mar",3,IF(MID(B2,4,3)="Apr",4,IF(MID(B2,4,3)="May",5,IF(MID(B2,4,3)="Jun",6,IF(MID(B2,4,3)="Jul",7,IF(MID(B2,4,3)="Aug",8,IF(MID(B2,4,3)="Sep",9,IF(MID(B2,4,3)="Oct",10,IF(MID(B2,4,3)="Nov",11,IF(MID(B2,4,3)="Dec",12,"")))))))))))),"-",MID(B2,1,2)))+TIMEVALUE(IF(MID(B2,8,1)=" ",RIGHT(B2,7),RIGHT(B2,8)))

and then change the type of the cell containing the formula to Date and chose the desirable format.

All in all a quite involved process. Is there a more automated way?

3 Answers 3

1

You could use some sort of "lookup table" to translate the month names to dates (this solution is based on a solution proposed for the opposite operation - translate numbers to month names)

Just put the month names one after each other into a column on your spreadsheet, and define a named range on those cells (e.g. Monthnames). Now, you can determine the month's number using =MATCH("Dec",Monthnames,0) which results in 12:

enter image description here

With such a table, you could replace the IF statements by a single MATCH call:

=DATEVALUE(CONCATENATE(MID(A2,8,2),"/",MATCH(MID(A2,4,3),Monthnames,0),"/",MID(A2,1,2)))

or on multiple lines:

=DATEVALUE( CONCATENATE( MID(A2,8,2), "/", MATCH( MID(A2,4,3), Monthnames, 0 ), "/", MID(A2,1,2) ) )

The formula above constructs a valid date value from your example strings:

enter image description here

1

Use MONTH to extract month value with dummy dd, yy values:

=DATEVALUE(CONCATENATE(MID(A2,8,2),"/",MONTH("1"&MID(A2,4,3)&"1"),"/",MID(A2,1,2)))
2
  • Remember to properly format your answer. Use a right angle bracket > before each line of a block quote, and indent code blocks with four spaces.
    – bwDraco
    Jan 24, 2015 at 2:05
  • Gary, your formula does not work out from the box but I can see what you mean, LibreOffice manual to the rescue, e.g. =MONTH(CONCATENATE("2000","-",MID(A2,4,3),"-","1")) or shorter =MONTH("1999"&"-"&MID(B2,4,3)&"-"&"1") (didn't know that concatenate trick). Clever, thanks! Nov 16, 2015 at 12:31
0

Try to add zero to the date. It worked for me when the date was in text format (ie had an apostrophe in front of the value) for data imported from a data file.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .