Ask Your Question
0

Selective ordinal for dates: NatNum12

asked 2020-09-19 18:24:28 +0100

Olivier gravatar image

I have a request for date format which is

The first day of the month must be ordinal (pt). 1º de janeiro de 2020

The second to 31st day of the month must be cardinal (pt): 2 de janeiro de 2020.

The solution for the first day is

[NatNum12 D=ordinal-number]D" de "MMMM" de "AAAA

But I need to put a condition for the other days of the month, in cardinal

D" de "MMMM" de "AAAA

My difficulty is to extract the 1st day of the month to be used in the format code.

Any help welcome.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2020-09-19 20:46:02 +0100

Opaque gravatar image

updated 2020-09-19 20:48:26 +0100

Hello

from LibreOffice Help - Number Format Codes - Conditions:

You can define a number format so that it only applies when the condition that you specify is met. Conditions are enclosed by square brackets [ ]

You can use any combination of numbers and the <, <=, >, >=, = and <> operators.

From this I conclude, that it is not possible to use any function in conditions of a format definition, but only these 6 operators. Hence your only choice is a Conditional Formatting using Format -> Conditional in combination with two different cell styles.

The procedure would be:

Step 1 - Create your desired Cell Styles

  • Open your document
  • Styles -> Manage Styles (or F11)
  • Click icon leftmost (beneath Styles), which is Cell Styles
  • Right click on Default Cell Style and New...
  • Provide a name (e.g. DateFirstDay)
  • Define the format required for the first day of a month in tab Numbers(i.e. using Format Code [NatNum12 D=ordinal-number]D" de "MMMM" de "AAAA)
  • Click OK

Repeat the last four steps for another cell style (e.g. DateOtherDay using Format Code D" de "MMMM" de "AAAA)

Step 2 - Define the Conditional Formatting

  • Select the cells which should contain the Conditional Formatting (e.g. A:A i.e. assuming column A contains your dates)
  • Format -> Conditional -> Condition...
  • Condtion 1 - Formula is - TEXT(A1;"D")="1"- Apply Style: DateFirstDay
  • Click button Add
  • Condtion 2 - Formula is - TEXT(A1;"D")<>"1"- Apply Style: DateOtherDay
  • Click button OK

Check also the following example file: C:\fakepath\CondPortugeseDates.ods

Tested using LibreOffice:

Version: 7.0.1.2; Build ID: 7cbcfc562f6eb6708b5ff7d7397325de9e764452
CPU threads: 8; OS: Linux 5.3; UI render: default; VCL: kf5
Locale: en-US (en_US.UTF-8); UI: en-US; Calc: threaded

Hope that helps.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-09-19 18:24:28 +0100

Seen: 40 times

Last updated: Sep 19