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
Take a look to this thread, maybe same help can be found there: Date format don't work after upgrade to 3.6 from 3.5
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
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:
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.
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 3.6.4.3 on XP/SP3