Make Day CAPS In Custom Date Format

[robzrob]

I've got a cell formatted thus: hh:mm ddd dd.mm.yy which returns 19:20
Thu 25.02.10. I want 19:20 THU 25.02.10, ie the day in caps.

 

[Fred Smith]

You need to use the Upper function, as in:
=UPPER(TEXT(A1,"hh:mm ddd dd.mm.yy"))

Regards,
Fred

 

[Ron Rosenfeld]

I've got a cell formatted thus: hh:mm ddd dd.mm.yy which returns 19:20
Thu 25.02.10. I want 19:20 THU 25.02.10, ie the day in caps.

I don't believe you can do that with formatting. I think the only way to get
that display is with a formula, such as:

=UPPER(TEXT(NOW(),"hh:mm ddd dd.mm.yy"))

Of course, now you are dealing with a text string which will make it difficult
to use in calculations downstream.

If that is important, you might separate your data/entry-computation cell from
the display cell.

--ron

 

[Rick Rothstein]

You can't do that with cell formatting and I'm thinking you don't want to
use a separate cell (as a formula would require)... you could do what you
want with VB code, but ONLY if the values in the cells are constants (that
is, not from a formula). To do this, you will have to tell us the range of
cells that would need this "format" (that is, what column or columns, row or
rows). If the dates are from a formula, you could have VB take over the
function of the formula (that is, not use a formula but let VB do the
calculation instead via event code), however you would have to show us your
formula in addition to telling us the range of cells involved) so we could
see how to replace it with event code.

 

[robzrob]

You need to use the Upper function, as in:
=UPPER(TEXT(A1,"hh:mm ddd dd.mm.yy"))

Regards,
Fred






- Show quoted text -

Thanks, it works - I didn't think it would because I've got =NOW() in
the cell - but it does!

 

[robzrob]

I don't think you'll be able to do it in formatting, but try
=UPPER(TEXT(A1,"hh:mm ddd dd.mm.yy "))
--
David Biddulph






- Show quoted text -

Thanks, it works - I didn't think it would because I've got =NOW() in
the cell - but it does!

 

[robzrob]

I don't believe you can do that with formatting.  I think the only way to get
that display is with a formula, such as:

=UPPER(TEXT(NOW(),"hh:mm ddd dd.mm.yy"))

Of course, now you are dealing with a text string which will make it difficult
to use in calculations downstream.

If that is important, you might separate your data/entry-computation cellfrom
the display cell.

--ron

Thanks, it works - I didn't think it would because I've got =NOW() in
the cell - but it does!

 

[robzrob]

You can't do that with cell formatting and I'm thinking you don't want to
use a separate cell (as a formula would require)... you could do what you
want with VB code, but ONLY if the values in the cells are constants (that
is, not from a formula). To do this, you will have to tell us the range of
cells that would need this "format" (that is, what column or columns, rowor
rows). If the dates are from a formula, you could have VB take over the
function of the formula (that is, not use a formula but let VB do the
calculation instead via event code), however you would have to show us your
formula in addition to telling us the range of cells involved) so we could
see how to replace it with event code.

--
Rick (MVP - Excel)






- Show quoted text -

Thanks, the UPPER...TEXT... formula works.