Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel weirdness (2002 SP2)

    I'm trying to get the month value from a date, but all I'm getting is 'Jan' or '1' depending on how I format the cell. I have a date in cell A1, formatted as a date, and when I enter the formula '=MONTH(A1)' in another cell all I get is the value for January, regardless of the actual date. I have tried the DAY and YEAR functions in other cells and they work correctly. What's going on here?

    Other weirdness: after the screensaver kicks in, when I move the mouse or hit a key, Excel comes back in a window about one quarter the size I had it before the screensaver started. That's with Excel in a window, not full screen. This only happens with Excel; Word and Outlook come back the way they were. Any ideas?

  2. #2
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel weirdness (2002 SP2)

    Please post a spreadsheet demonstrating your problem.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel weirdness (2002 SP2)

    If you have a date in A1 and then B1=Month(A1) should return 1 or Jan, as long as the month of your date in A1 is January something. When you change A1 to July something, then B1 should return 7 or Jul.

    If this is NOT happening take a close look at A1. Is it a real date? or perhaps text?

    Paul

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel weirdness (2002 SP2)

    If you format cell A1 as "general" what does it display?

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel weirdness (2002 SP2)

    OK, here's a file called dates.xls

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel weirdness (2002 SP2)

    It's a real date. See the file dates.xls I have posted

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel weirdness (2002 SP2)

    If the cell containing the date is formatted as general, it displays the date in serial number format. See the file dates.xls I have posted.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel weirdness (2002 SP2)

    The value in the cells in C are correct. You have them formatted as "mmm"

    The month is 10 (c11) for cell A11. You formatted the value 10 to "mmm" so it formats it as a date and the 10th day is Jan 10,1900 so displayed as "mmm" it is Jan.

    You can display a11 as "mmm" and it will display Oct. so instead of month(a11) just use = A11 and format as "mmm"

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel weirdness (2002 SP2)

    OK, but that doesn't help. I want to display the month as 3 letters (Oct, Jan, etc.). If I format the day column as dd, it displays the date (26, 27, etc.); if I format it as ddd, it displays the day of the week (Mon, Tue, etc.). How do I get the month to display as 3 letters?

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel weirdness (2002 SP2)

    Format it as "mmm".
    Legare Coleman

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel weirdness (2002 SP2)

    Steve gave you the answer in <post#=306188>post 306188</post#>. I'll spell out the details for you.

    Don't use formulas like =MONTH(A1) in column C.
    Instead, enter =A1 in cell C1, and fill down to C12.
    Select C1:C12.
    Select Format | Cells..., Number tab.
    Select the Custom category.
    Enter mmm in the Type box.
    Click OK

  12. #12
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Excel weirdness (2002 SP2)

    <P ID="edit" class=small>(Edited by MarkD on 25-Oct-03 10:58. Added additional note.)</P>One additional note, if you can't use cell formatting, such as when concatenating the date in a text string, you can use the TEXT function to specify correct format for any numerical value (including dates). Example:

    Cell A1 = 1/1/2003
    Formula =TEXT(A1,"mmm") returns "Jan"
    Cell A365 = 12/31/2003
    Formula =TEXT(A365,"mmm") returns "Dec"

    I had similar problem when trying to format date value (mm/dd/yyyy) as an "ordinal" date, ie, 1/1/2003 = January 1st, 2003, or 12/31/2003 = December 31st, 2003, etc. Since this "ordinal" date is derived by concatenating various values together could not rely on cell formatting to format the month portion of date. Wound up with a somewhat convoluted formula (the ordinal number part of equation borrowed from Walkenbach or I'd still be trying to get the formula correct):

    =TEXT($A1,"mmmm")&" "&DAY($A1)&IF(OR(VALUE(RIGHT(DAY($A1),2))={11,12,1 3}),"th",IF(OR(VALUE(RIGHT(DAY($A1)))={1,2,3}),CHO OSE(RIGHT(DAY($A1)),"st","nd","rd"),"th"))&", "&YEAR(($A1))

    where column A contains the actual date values. The formula returns "date" (text) as January 1st, 2003, etc. Note use of TEXT function to return month, spelled out as "January", etc by specifying "mmmm" as format.

    PS: Note that as name implies, the value returned by the TEXT function is text, not a number, and therefore cannot be used in calculations, etc.

    HTH

  13. #13
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel weirdness (2002 SP2)

    OK, I don't need to use a function at all, just format the cell the way I want it to appear. However, that doesn't solve my problem. The reason I was trying to use a function was because I am trying to calculate the number of whole months between two dates. The formula =(A1-A2)/(365/12)-0.5 isn't accurate, as it doesn't take account of leap years. For example, if the two dates are 3/17/1994 and 11/16/2006, this formula produces the result 152, but the correct result is 151.

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel weirdness (2002 SP2)

    Try
    =DATEDIF(startdate,enddate,"m")
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel weirdness (2002 SP2)

    Thanks. That's exactly what I needed. But where is it documented? It doesn't show up in the list of available functions. Are there any other undocumented functions?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •