Date Format - Formula Problem
 

I setup a spreadsheet to automatically fill in a calendar for our company
fiscal year.

All the calculations run off the date entered into B2. The cell is formated
to show the date in "mmm" format. Cell B3 = B2, but is formated as "d".
Then cell C2 and C3 are the next day, and so on.

When I enter a date into B2, it shows the correct abbreviation, but then all
the formulas return a #VALUE! error and cell B2 changes from the date to the
actual abbreviation that it is formated for.

For example, I enter 1/1/2007 and hit enter. The cell now reads "JAN" in
both the cell and the formula bar (where it should still read 1/1/2007).
Which then throws off all the other formulas.

Any ideas how to fix this?

Date Format - Formula Problem
 

Sounds like you have some event code changing. Right-click the sheet tab
name and select code, check if there is code there.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Josh O." wrote in message
...
I setup a spreadsheet to automatically fill in a calendar for our company
fiscal year.

All the calculations run off the date entered into B2. The cell is
formated
to show the date in "mmm" format. Cell B3 = B2, but is formated as "d".
Then cell C2 and C3 are the next day, and so on.

When I enter a date into B2, it shows the correct abbreviation, but then
all
the formulas return a #VALUE! error and cell B2 changes from the date to
the
actual abbreviation that it is formated for.

For example, I enter 1/1/2007 and hit enter. The cell now reads "JAN" in
both the cell and the formula bar (where it should still read 1/1/2007).
Which then throws off all the other formulas.

Any ideas how to fix this?

Date Format - Formula Problem
 

Yes there is.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 2 Then Exit Sub
Target = UCase(Format(Target, "mmm"))

End Sub

Looks like it is changing the "Jan" to "JAN"...which I do actually want it
to do. Is there a way to do this that doesn't replace the formula, just the
format?

"Bob Phillips" wrote:

Sounds like you have some event code changing. Right-click the sheet tab
name and select code, check if there is code there.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Josh O." wrote in message
...
I setup a spreadsheet to automatically fill in a calendar for our company
fiscal year.

All the calculations run off the date entered into B2. The cell is
formated
to show the date in "mmm" format. Cell B3 = B2, but is formated as "d".
Then cell C2 and C3 are the next day, and so on.

When I enter a date into B2, it shows the correct abbreviation, but then
all
the formulas return a #VALUE! error and cell B2 changes from the date to
the
actual abbreviation that it is formated for.

For example, I enter 1/1/2007 and hit enter. The cell now reads "JAN" in
both the cell and the formula bar (where it should still read 1/1/2007).
Which then throws off all the other formulas.

Any ideas how to fix this?

Date Format - Formula Problem
 

It is not only upshifting it, it is removing the formula. I am afraid there
is no way to keep the formula and upshift. Ditch the code is my advice.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Josh O." wrote in message
...
Yes there is.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 2 Then Exit Sub
Target = UCase(Format(Target, "mmm"))

End Sub

Looks like it is changing the "Jan" to "JAN"...which I do actually want it
to do. Is there a way to do this that doesn't replace the formula, just
the
format?

"Bob Phillips" wrote:

Sounds like you have some event code changing. Right-click the sheet tab
name and select code, check if there is code there.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Josh O." wrote in message
...
I setup a spreadsheet to automatically fill in a calendar for our
company
fiscal year.

All the calculations run off the date entered into B2. The cell is
formated
to show the date in "mmm" format. Cell B3 = B2, but is formated as
"d".
Then cell C2 and C3 are the next day, and so on.

When I enter a date into B2, it shows the correct abbreviation, but
then
all
the formulas return a #VALUE! error and cell B2 changes from the date
to
the
actual abbreviation that it is formated for.

For example, I enter 1/1/2007 and hit enter. The cell now reads "JAN"
in
both the cell and the formula bar (where it should still read
1/1/2007).
Which then throws off all the other formulas.

Any ideas how to fix this?

Date Format - Formula Problem
 

Get rid of the code first. It looks as though the code is only affecting
column B.

Anyway, with your date in B2, you might try this formula in B3.
=UPPER(TEXT(B2,"mmm"))


HTH,
Paul



"Josh O." wrote in message
...
Yes there is.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 2 Then Exit Sub
Target = UCase(Format(Target, "mmm"))

End Sub

Looks like it is changing the "Jan" to "JAN"...which I do actually want it
to do. Is there a way to do this that doesn't replace the formula, just
the
format?

"Bob Phillips" wrote:

Sounds like you have some event code changing. Right-click the sheet tab
name and select code, check if there is code there.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Josh O." wrote in message
...
I setup a spreadsheet to automatically fill in a calendar for our
company
fiscal year.

All the calculations run off the date entered into B2. The cell is
formated
to show the date in "mmm" format. Cell B3 = B2, but is formated as
"d".
Then cell C2 and C3 are the next day, and so on.

When I enter a date into B2, it shows the correct abbreviation, but
then
all
the formulas return a #VALUE! error and cell B2 changes from the date
to
the
actual abbreviation that it is formated for.

For example, I enter 1/1/2007 and hit enter. The cell now reads "JAN"
in
both the cell and the formula bar (where it should still read
1/1/2007).
Which then throws off all the other formulas.

Any ideas how to fix this?