Custom Date Format with Time Format Optional

David

This is impossible without VBA as far as I can see, sorry.

Hello David,

Not quite what you want but perhaps this would help.....

If you use the following custom format

[>1]mmm, m/d/yyyy;;h:mm AM/PM;

then if you enter a time it will only show the time, if you enter a date it will show the date....

barry

I can't believe that works.

But then again I never realised you could just enter the time as 5 p.

I thought Excel would see that as text, hence the suggestion that code would be needed.

Thanks for the quick replies, Norie and Barry.

Barry: I cut and pasted your suggestion:

[>1]mmm, m/d/yyyy;;h:mm AM/PM;

Entering the time alone works perfectly. For some reason, when I enter the date, it displays the three-letter abbreviation for the month instead of the day of the week. For instance, if I enter "10/1," it displays "Oct, 10/1/2007." I tried adding a fourth "m," a space before "mmm," but nothing seems to make it return "Mon, 10/1/2007."

It also won't display an entry that includes both date and time. If I look in the formula bar, it's reading it correctly ("10/1 5 p" becomes "10/1/2007 05:00:00 PM"), but drops off the time in display. Not a problem if that's the best Excel's custom number format will allow.

Finally, if you have time to educate me, would you explain the syntax? What does [>1] do? Also, what role do the double semi-colons and the ending semi-colon play?

And Norie, I found out about the "5 p" capability by pure accident one day years ago. It's a big help. Amazing what hours of obsession turn up!

Thanks a bunch.

David

I'm still trying to work out how I got the above to work.....

If you want day of week change to this slightly amended version, using ddd instead of mmm

[>1]ddd, m/d/yyyy;[<=1]h:mm AM/PM

Basically because a day is 1 in excel any time is less than 1 and any date is greater than 1 so the above formats values greater than 1 as dates and those less than 1 as times....but I don't know a way of making it show dates and times automatically, sorry

Barry:

Okay, I feel like a space cadet. Of course I'm supposed to use "d" not "m." Thanks for being so gracious about my momentary brain lapse.

Dates AND times would be nice, but you've already gotten me farther than a I thought possible. Thanks for the assist.

David