SOLVED

# Date time custom format to suppress time if not entered

Brass Contributor

# Date time custom format to suppress time if not entered

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?

Thank You

Zdenek Moravec

Cesky Krumlov, Czechia

2 Replies

# Re: Date time custom format to suppress time if not entered

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

# Re: Date time custom format to suppress time if not entered

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

# Re: Date time custom format to suppress time if not entered

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.