Read displayed, rather than raw, date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

paulkaye - 06 Dec 2007 12:45 GMT

I have a row of months which, for ease, I have allowed Excel to
interpret as dates. The month is displayed as e.g. "Dec-07" but the
'raw' data in the cell is e.g. "01/12/07". For reasons that are not
important here, I need to be able to perform conditional functions
using the displayed "Dec-07". Is there any way to do this?

Thanks in advance!

Paul

Reply to this Message

Bernard Liengme - 06 Dec 2007 12:59 GMT

What "conditional functions"? Please be specific
If A1 holds 01/12/07 and displays "Dec-07" then the cell is formatted with
"mmm-yy"
To extract the month number use MONTH(A1)
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

>I have a row of months which, for ease, I have allowed Excel to
> interpret as dates. The month is displayed as e.g. "Dec-07" but the
[quoted text clipped - 5 lines]
>
> Paul

Reply to this Message

paulkaye - 06 Dec 2007 13:12 GMT

I am using this cell to refer to other worksheets using the INDIRECT
funtion. The worksheets are labelled "Dec-07" etc. and everything
works fine if the cell contains text saying "Dec-07" but not if it's a
date (presumably because the cell contains 01/12/07). In order to be
able to expand the spreadsheet over years, it would be helpful to
retain these cells as dates so that Excel can automatically fill in
the next cells correctly.

I hope that clarifies my question!

Paul

On Dec 6, 2:59 pm, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:

> What "conditional functions"? Please be specific
> If A1 holds 01/12/07 and displays "Dec-07" then the cell is formatted with
[quoted text clipped - 15 lines]
>
> > Paul

Reply to this Message

Bernard Liengme - 06 Dec 2007 13:33 GMT

Cell A3 displays 1/12/2007 (I use the international date format)
I could format it to show Dec-07
Cell B3 has formula =INDIRECT("'"&TEXT(A3,"mmm-yy")&"'!A1")
It picks up the value from A1 on the Dec-07 sheet

After the INDIRECT( there is a double quote, a single quote, then a double
quote
After the second & there is a double quote then a single quote
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

>I am using this cell to refer to other worksheets using the INDIRECT
> funtion. The worksheets are labelled "Dec-07" etc. and everything
[quoted text clipped - 30 lines]
>>
>> > Paul

Reply to this Message

paulkaye - 06 Dec 2007 13:43 GMT

Thanks!

On Dec 6, 3:33 pm, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:

> Cell A3 displays 1/12/2007 (I use the international date format)
> I could format it to show Dec-07
[quoted text clipped - 48 lines]
>
> >> > Paul

Reply to this Message

Ron Rosenfeld - 06 Dec 2007 13:11 GMT

>I have a row of months which, for ease, I have allowed Excel to
>interpret as dates. The month is displayed as e.g. "Dec-07" but the
[quoted text clipped - 5 lines]
>
>Paul

You can convert a date to the Text string by using the formula:

=TEXT(date,"mmm-yy")

But perhaps if you are more specific about the "conditional function" you want
to perform, a better solution may be offered.
--ron

Reply to this Message

paulkaye - 06 Dec 2007 13:21 GMT

I think this will work but I've got another problem - this is giving
me the mmm-yy text in another language that is installed on my system.
How can I ensure it uses English?!

> >I have a row of months which, for ease, I have allowed Excel to
> >interpret as dates. The month is displayed as e.g. "Dec-07" but the
[quoted text clipped - 13 lines]
> to perform, a better solution may be offered.
> --ron

Reply to this Message

paulkaye - 06 Dec 2007 13:28 GMT

Ah, I had a look at another spreadsheet where something similar had
been done and found B1mmm-yy did the trick. I have no idea why though.
Can you explain what the B1 is?!

> I think this will work but I've got another problem - this is giving
> me the mmm-yy text in another language that is installed on my system.
[quoted text clipped - 17 lines]
> > to perform, a better solution may be offered.
> > --ron

Reply to this Message

Ron Rosenfeld - 06 Dec 2007 13:54 GMT

>Ah, I had a look at another spreadsheet where something similar had
>been done and found B1mmm-yy did the trick. I have no idea why though.
>Can you explain what the B1 is?!

B1 ensures you will be using the Gregorian calendar (as opposed to using the
Hijiri calendar). If I understand it correctly, it probably will return the
results in the language specified by your Control Panel/Regional and Language
Settings.

I am more familiar with the details on:

http://office.microsoft.com/en-us/excel/HA010346351033.aspx?pid=CL100570551033#Locale

--ron

Reply to this Message

Rate this thread: