Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
making numerical date return mmmm format
I have one cell formatted as a number and I am trying to get it to
format to a different cell as mmmm and I need it to update everytime I change the numerical cell. Example: A1 has 1 as number A10 is referring to A1 with custom format of mmmm which returns January. My problem is that when I change A1 to 2 A10 does not update to show February. Please help... am I doing something wrong? Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
making numerical date return mmmm format
Just use a custom format. Go to Format - Cells - Number - Custom then enter
mmmm. " wrote: I have one cell formatted as a number and I am trying to get it to format to a different cell as mmmm and I need it to update everytime I change the numerical cell. Example: A1 has 1 as number A10 is referring to A1 with custom format of mmmm which returns January. My problem is that when I change A1 to 2 A10 does not update to show February. Please help... am I doing something wrong? Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
making numerical date return mmmm format
Put this formula in A10:
=DATE(2009,A1,1) then it should follow changes to A1. I assume at the moment that you have: =A1 in A10 - this is assumed (by Excel) to represent 1st Jan 1900, and so if you change A1 to 2 then this would be 2nd Jan 1900, so your month has not changed. Hope this helps. Pete On Feb 9, 5:01*pm, wrote: I have one cell formatted as a number and I am trying to get it to format to a different cell as mmmm and I need it to update everytime I change the numerical cell. Example: A1 has 1 as number A10 is referring to A1 with custom format of mmmm which returns January. My problem is that when I change A1 to 2 A10 does not update to show February. Please help... am I doing something wrong? Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
making numerical date return mmmm format
On Feb 9, 9:12*am, Pete_UK wrote:
Put this formula in A10: =DATE(2009,A1,1) then it should follow changes to A1. I assume at the moment that you have: =A1 in A10 - this is assumed (by Excel) to represent 1st Jan 1900, and so if you change A1 to 2 then this would be 2nd Jan 1900, so your month has not changed. Hope this helps. Pete On Feb 9, 5:01*pm, wrote: I have one cell formatted as a number and I am trying to get it to format to a different cell as mmmm and I need it to update everytime I change the numerical cell. Example: A1 has 1 as number A10 is referring to A1 with custom format of mmmm which returns January. My problem is that when I change A1 to 2 A10 does not update to show February. Please help... am I doing something wrong? Thanks in advance.- Hide quoted text - - Show quoted text - Thanks a ton, that works perfectly! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
making numerical date return mmmm format
Hi,
You've got a solution, but no one explained why your approach didn't work: Excel stores dates as numbers starting with January 1, 1900 being day 1. Each successive day is one number higher. So you got lucky with 1, but 2 is January 2, 1900, so you didn't get February. Here is another solution: =--(A1&"-1") Put this formula in A10 and format the cell to MMMM. -- If this helps, please click the Yes button Cheers, Shane Devenshire " wrote: I have one cell formatted as a number and I am trying to get it to format to a different cell as mmmm and I need it to update everytime I change the numerical cell. Example: A1 has 1 as number A10 is referring to A1 with custom format of mmmm which returns January. My problem is that when I change A1 to 2 A10 does not update to show February. Please help... am I doing something wrong? Thanks in advance. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
making numerical date return mmmm format
You're welcome, Angela - thanks for feeding back.
Pete On Feb 9, 5:17*pm, AMG wrote: Thanks a ton, that works perfectly |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
making numerical date return mmmm format
Another possibility, which does not require the cell to be formatted, would
be... =TEXT(28*A1,"mmmm") -- Rick (MVP - Excel) "Shane Devenshire" wrote in message ... Hi, You've got a solution, but no one explained why your approach didn't work: Excel stores dates as numbers starting with January 1, 1900 being day 1. Each successive day is one number higher. So you got lucky with 1, but 2 is January 2, 1900, so you didn't get February. Here is another solution: =--(A1&"-1") Put this formula in A10 and format the cell to MMMM. -- If this helps, please click the Yes button Cheers, Shane Devenshire " wrote: I have one cell formatted as a number and I am trying to get it to format to a different cell as mmmm and I need it to update everytime I change the numerical cell. Example: A1 has 1 as number A10 is referring to A1 with custom format of mmmm which returns January. My problem is that when I change A1 to 2 A10 does not update to show February. Please help... am I doing something wrong? Thanks in advance. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
making numerical date return mmmm format
Shane,
I thought I explained what was happening to the OP. Anyway, your proposed solution doesn't work in the UK as we enter days first and the "-" symbol is not normally used in dates. Pete On Feb 9, 5:38*pm, Shane Devenshire wrote: Hi, You've got a solution, but no one explained why your approach didn't work: Excel stores dates as numbers starting with January 1, 1900 being day 1. * Each successive day is one number higher. *So you got lucky with 1, but 2 is January 2, 1900, so you didn't get February. Here is another solution: =--(A1&"-1") Put this formula in A10 and format the cell to MMMM. -- If this helps, please click the Yes button Cheers, Shane Devenshire " wrote: I have one cell formatted as a number and I am trying to get it to format to a different cell as mmmm and I need it to update everytime I change the numerical cell. Example: A1 has 1 as number A10 is referring to A1 with custom format of mmmm which returns January. My problem is that when I change A1 to 2 A10 does not update to show February. Please help... am I doing something wrong? Thanks in advance.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return a different date format | Excel Worksheet Functions | |||
I want my combo box to return a date format instead of a number? | Excel Discussion (Misc queries) | |||
Are you kidding me??? Are you telling me I can't sort format: dddd, mmmm dd, yyyy? | Excel Discussion (Misc queries) | |||
using lookup to return more than one answer in date format | Excel Worksheet Functions | |||
Return Numerical Label for LAST value Subtracted to reach Sum Target Value | Excel Worksheet Functions |