Forum Discussion
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
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.
2 Replies
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.
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.
