Read displayed, rather than raw, date

  • Thread starter Thread starter paulkaye
  • Start date Start date
P

paulkaye

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
 
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
 
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
Click to expand...

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
 
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
 
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?!
 
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?!
 
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
 
Thanks!

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
Click to expand...
 
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?!
Click to expand...

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
 
Back
Top