Forum Discussion

Zdenek_Moravec's avatar
Zdenek_Moravec
Brass Contributor
Jan 29, 2024
Solved

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...
excel
  • Zdenek_Moravec's avatar
    Zdenek_Moravec
    Feb 02, 2024

    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.

     

Zdenek_Moravec's avatar
Zdenek_Moravec
Brass Contributor
Feb 02, 2024

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.

 

Resources