Ask Your Question

i want to enter a date in format DD MMM, YYYY HH:MM:SS [closed]

asked 2012-12-29 14:05:40 +0200

wswalker gravatar image

updated 2012-12-29 14:06:15 +0200

In excel , if I paste a date in format DD MMM, YYYY HH:MM:SS

18 Dec, 2012 21:34:47

it is recognised as a date in excel automatically

BUT in Libre office - its seen as text and i have to edit it

any way to set the format to allow 18 Dec, 2012 21:34:47 to be seen as a date and do calculations on

I have tried various formats

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-19 19:24:35.067049


Take a look to this thread, maybe same help can be found there:

m.a.riosv gravatar imagem.a.riosv ( 2012-12-30 03:07:49 +0200 )edit

i dont understand what format you used

i am pasted the text 18 Dec, 2012 21:34:47 from another system into a cell and what it to be seen as a date

sorry I did not follow your instructions

wswalker gravatar imagewswalker ( 2012-12-31 17:18:24 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2013-01-03 07:06:03 +0200

Marce gravatar image

As ROSt52's tests show, the user-defined cell format "DD MMM, YYYY HH:MM:SS" can be employed to show a date/time entered in another format, or the result of a calculation involving dates/times. But, unlike predefined formats, when a date/time is entered in a cell in this format, it is not interpreted as such by Calc, and so formulas referencing it will fail. The only workaround I could think of was a function to convert the date/time to one of the predefined formats:

Function ADATE(nadate)

'Function ADATE converts from "DD MMM, YYYY HH:MM:SS" to "MM/DD/YYYY HH:MM:SS".

nadate = Trim(nadate)

ADATE = MM(Mid(nadate,4,3)) & "/" & Left(nadate,2) & "/" & _
    Mid(nadate,9,4) & " " & Right(nadate,8)

End Function

Function MM(MMM)

'The output of auxiliary function MM is a string of the two-digit numerical month code.

Select Case MMM

    Case "Jan"

    MM = "01"

    Case "Feb"

    MM = "02"

    Case "Mar"

    MM = "03"

    Case "Apr"

    MM = "04"

    Case "May"

    MM = "05"

    Case "Jun"

    MM = "06"

    Case "Jul"

    MM = "07"

    Case "Aug"

    MM = "08"

    Case "Sep"

    MM = "09"

    Case "Oct"

    MM = "10"

    Case "Nov"

    MM = "11"

    Case "Dec"

    MM = "12"

End Select

End Function

The screenshot below shows function ADATE in action:

image description

Cell C2 contains a date/time in "DD MMM, YYYY HH:MM:SS" format. Cell D2 has the formula "=ADATE(C2)" that converts the contents of C2 to "MM/DD/YYYY HH:MM:SS". In cell E2 the formula "=D2+1" yields the expected result. Notice also that cell E2 has the user-defined format "DD MMM, YYYY HH:MM:SS", which in this case works because it operates on the results of a formula evaluation.

edit flag offensive delete link more

answered 2012-12-31 05:24:38 +0200

ROSt52 gravatar image

I made test:

-copied your format from this page into the cell format window selecting
-format a sufficient number of cells with this format
-entered date by keying in 12/12/31
=> result: 31 Dec, 2012 00:00:00
-entered date and time by keying in 12/12/31 13:09:25
=> result: 31 Dec, 2012 13:09:25
-entered date by shortcut ctrl+;
=> result: actual date and time in correct format
-entered date by shortcut ctrl++
=> result: actual date and time in correct format

next I added to all the results in a separate cell +1 and obtained the correct date and time in standard date format (thus Calc recognized the result as date)

next I reformated the calculate results wiht your format and received all result in the new format and no difference in date and time indicated.

LibO on XP/SP3

edit flag offensive delete link more

Question Tools


Asked: 2012-12-29 14:05:40 +0200

Seen: 5,541 times

Last updated: Jan 03 '13