SOLVED

Date time custom format to suppress time if not entered

Brass Contributor

Dear colleagues

I have a column with a custom format dd.mm.rrrr hh:mm

If the time is not entered, the time portion should be suppressed.

Is it possible with custom format?

Zdenek_Moravec_0-1706541745308.png

Thank You

Zdenek Moravec

Cesky Krumlov, Czechia

2 Replies

@Zdenek_Moravec 

No, you cannot use a custom format for this.

You'd have to use VBA code to set the number format to dd.mm.yyyy if the entry is a whole number, and to dd.mm.yyyy hh:mm otherwise.

best response confirmed by HansVogelaar (MVP)
Solution

Because the date with time is a decimal number, I was first thinking about using custom format conditions ([is not decimal]dd.mm.rrrr;[is decimal]dd.mm.rrrr hh:mm;), but the conditon can be only number, not formula.

So the working solution is to use conditional format with formula =MOD(A1;1)=0. Default cell format is dd.mm.rrrr hh:mm, for dates without time is the condition met and applying the format dd.mm.rrrr. Solved.

 

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

Because the date with time is a decimal number, I was first thinking about using custom format conditions ([is not decimal]dd.mm.rrrr;[is decimal]dd.mm.rrrr hh:mm;), but the conditon can be only number, not formula.

So the working solution is to use conditional format with formula =MOD(A1;1)=0. Default cell format is dd.mm.rrrr hh:mm, for dates without time is the condition met and applying the format dd.mm.rrrr. Solved.

 

View solution in original post