Conditional date formatting based on cells in a different column

ROSt52 · June 12, 2025, 2:24am

I have several columns with different dates and date formats and a column with text. The final column should get a date and date format depending on the text in the text column. How do i have to set up the conditional formatting for the date format?

details
Column E contains a date with the format yyyy/mm
Column F contains either text “regular” or “one-time”
Column G contains a date with format yyyy/mm/dd (days are additional compare to column E)
Column I has to show the date and the date format of E or G depending on the content of the column F

eg.
E2 contains a date in format yyyy/mm
G2 contains a different date in format yyyy/mm/dd
If F2 contains text “regular”
Then i2 should display the date of E2 with the format of E2 (yyyy/mm)
If F2 contains text “one-time”,
Then I2 should display the date of G2 with the format of G2 (yyyy/mm/dd)

With an IF-statement I can pick up the correct date. However I cannot get the right format into the cells of column I.

How can I get the correct date format in the cells of column I depending on the content of the cells in column F2.

Thanks in advance for helpful replies!

Zizi64 · June 12, 2025, 3:18am

You must create two Cell Style for the date. Then you need apply the actual Style by the Conditional Format feature . From the menu:
“Format - Conditional - Condition”

Use the the “Formula is” option (instead of the “Cell value” option).

koyotak · June 12, 2025, 9:20am

an example @Zizi64

000_LO-CALC_logical decision of formattings of datums_111909.ods (19.1 KB)

Villeroy · June 12, 2025, 10:18am

ask123111.ods (13.2 KB)

ROSt52 · June 14, 2025, 7:07am

Hi all who replied to my question. You all contributed to my understanding of the STYLE function. Thanks to all of you.

Villeroy · June 14, 2025, 8:05am

However, the style function is not as important as the styles.
See https://nextcloud.documentfoundation.org/s/o3P3XQoqdtB783e (chapter 4).

Same with conditional formatting:
ask123111_cf.ods (13.4 KB)

erAck · June 17, 2025, 3:37pm

Please do not use / recommend the use of the STYLE() function. It’s a legacy and IMHO should rather be deprecated. It sets the cell style during formula evaluation, whenever a recalc is triggered for that formula, whereas conditional formatting is evaluated for the current view only, not replacing an applied cell style. STYLE() is also not interoperable with other spreadsheet implementations. The only real use of STYLE() is to temporarily highlight a cell with a timeout along with the call of an asynchronous Add-In function when that delivers its result.