# Selective ordinal for dates: NatNum12

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 close merge delete

Sort by » oldest newest most voted

Hello

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

• 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.

more