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.

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

Regards,
Fred

"robzrob" wrote in message
...
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 think you'll be able to do it in formatting, but try
=UPPER(TEXT(A1,"hh:mm ddd dd.mm.yy "))
--
David Biddulph


"robzrob" wrote in message
...
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.

On Thu, 25 Feb 2010 11:22:04 -0800 (PST), robzrob wrote:

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

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.

--
Rick (MVP - Excel)


"robzrob" wrote in message
...
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.

On Feb 25, 8:01*pm, "Fred Smith" wrote:
You need to use the Upper function, as in:
=UPPER(TEXT(A1,"hh:mm ddd dd.mm.yy"))

Regards,
Fred

"robzrob" wrote in message

...



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.- Hide quoted text -

- Show quoted text -

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

On Feb 25, 8:05*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
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

"robzrob" wrote in message

...



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.- Hide quoted text -

- Show quoted text -

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

On Feb 25, 8:12*pm, Ron Rosenfeld wrote:
On Thu, 25 Feb 2010 11:22:04 -0800 (PST), robzrob wrote:
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

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

On Feb 25, 8:21*pm, "Rick Rothstein"
wrote:
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.

--
Rick (MVP - Excel)

"robzrob" wrote in message

...



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.- Hide quoted text -

- Show quoted text -

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