Excel weirdness (2002 SP2)

Topic

New Reply

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?

Reply | Quote

Replies

Please post a spreadsheet demonstrating your problem.

Reply | Quote

OK, here’s a file called dates.xls

Reply | Quote

OK, here’s a file called dates.xls

Reply | Quote

Please post a spreadsheet demonstrating your problem.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Steve

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

Format it as “mmm”.

Reply | Quote

Format it as “mmm”.

Reply | Quote

Steve gave you the answer in post 306188. 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

Reply | Quote

(Edited by MarkD on 25-Oct-03 10:58. Added additional note.)

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,13}),”th”,IF(OR(VALUE(RIGHT(DAY($A1)))={1,2,3}),CHOOSE(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

Reply | Quote

(Edited by MarkD on 25-Oct-03 10:58. Added additional note.)

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,13}),”th”,IF(OR(VALUE(RIGHT(DAY($A1)))={1,2,3}),CHOOSE(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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Try
=DATEDIF(startdate,enddate,”m”)

Reply | Quote

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?

Reply | Quote

DATEDIF is a strange case. It has been available in all recent versions of Excel, but it has been documented only in the online help for Excel 2000, not in Excel 97 or Excel 2002. How’s that for consistency?

See The DATEDIF Function for details.

Reply | Quote

You could also try =INT(12*YEARFRAC(date1,date2))

Reply | Quote

Your formula is promising, but it doesn’t return the same result as DATEDIF:

Date1	Date2	DATEDIF	YEARFRAC
02/28/2003	03/29/2003	1	0

The DATEDIF formula returns the correct result. I tried setting the third argument of YEARFRAC, but couldn’t find a setting that always returns the correct value.

Reply | Quote

I don’t think DATEDIF always gives the expected answer. Here is a quick example:

12/31/2002 1/31/2003 1 1
12/31/2002 2/28/2003 1 1.93333333333333
12/31/2002 3/31/2003 3 3
12/31/2002 4/30/2003 3 4
12/31/2002 5/31/2003 5 5
12/31/2002 6/30/2003 5 6
12/31/2002 7/31/2003 7 7
12/31/2002 8/31/2003 8 8
12/31/2002 9/30/2003 8 9

I apologize if the above is hard to read, but the first 2 columns are dates, the third column is =DATEDIF(a1,b1,”m”), and the fourth column is =12*YEARFRAC(A1,B1). It appears that DATEDIF only increments when the day of the second date equals the day of the first date. While this is easy enough to explain, most people would say that there are 2 whole months from 12/31/02 to 2/28/03. In this specific example, I think YEARFRAC gives better answers, except for the second row. In other examples, DATEDIF would do better. It’s clear to me that both options give imperfect results. I think this discussion indicates that you need to be very careful with counting months since the definition of a month varies.

Reply | Quote

Thanks for these examples. I agree with your conclusion.

Reply | Quote

Thanks for these examples. I agree with your conclusion.

Reply | Quote

These are exactly what I would expect from datedif.

It is like a birthday. You do not go up a year until the exact date. Anytime before your birthday, you “have not aged”.

When you have:
12/31/2002 to 4/30/2003
You have not reached the 31st day of April so you have not finished the 4th month, so it is only 3 months difference. the function does not round, it truncates (again. like a birthdate)

You can do alot more with datediff: this formula (eg):
=DATEDIF(A1,NOW(),”y”) & ” years, ” & DATEDIF(A1,NOW(),”ym”) & ” months, ” & DATEDIF(A1,NOW(),”md”) & ” days”

will display the number years, months, and days from the date in A1 to now.

I do agree whole-heartedly with your point, it is important to know what you are asking for before you pick a formula.

Steve

Reply | Quote

These are exactly what I would expect from datedif.

It is like a birthday. You do not go up a year until the exact date. Anytime before your birthday, you “have not aged”.

When you have:
12/31/2002 to 4/30/2003
You have not reached the 31st day of April so you have not finished the 4th month, so it is only 3 months difference. the function does not round, it truncates (again. like a birthdate)

You can do alot more with datediff: this formula (eg):
=DATEDIF(A1,NOW(),”y”) & ” years, ” & DATEDIF(A1,NOW(),”ym”) & ” months, ” & DATEDIF(A1,NOW(),”md”) & ” days”

will display the number years, months, and days from the date in A1 to now.

I do agree whole-heartedly with your point, it is important to know what you are asking for before you pick a formula.

Steve

Reply | Quote

Very Interesting! Now try this formula:

=DATEDIF(0,B3,”m”)-DATEDIF(0,A3,”m”)

Microsoft was very lazy in designing DATEDIF!

Reply | Quote

[indent]

---

Microsoft was very lazy in designing DATEDIF!

---

[/indent]I guess you have to blame Lotus123 for that, Datedif is only there for reasons of compatibility with that product. The same reason why Excel’s dates are wrong for 1900, thinking 1900 is a leap-year. This too is inherited for compatibility reasons (the wrong reason, I agree)

Reply | Quote

[indent]

---

Microsoft was very lazy in designing DATEDIF!

---

[/indent]I guess you have to blame Lotus123 for that, Datedif is only there for reasons of compatibility with that product. The same reason why Excel’s dates are wrong for 1900, thinking 1900 is a leap-year. This too is inherited for compatibility reasons (the wrong reason, I agree)

Reply | Quote

Very Interesting! Now try this formula:

=DATEDIF(0,B3,”m”)-DATEDIF(0,A3,”m”)

Microsoft was very lazy in designing DATEDIF!

Reply | Quote

I don’t think DATEDIF always gives the expected answer. Here is a quick example:

12/31/2002 1/31/2003 1 1
12/31/2002 2/28/2003 1 1.93333333333333
12/31/2002 3/31/2003 3 3
12/31/2002 4/30/2003 3 4
12/31/2002 5/31/2003 5 5
12/31/2002 6/30/2003 5 6
12/31/2002 7/31/2003 7 7
12/31/2002 8/31/2003 8 8
12/31/2002 9/30/2003 8 9

I apologize if the above is hard to read, but the first 2 columns are dates, the third column is =DATEDIF(a1,b1,”m”), and the fourth column is =12*YEARFRAC(A1,B1). It appears that DATEDIF only increments when the day of the second date equals the day of the first date. While this is easy enough to explain, most people would say that there are 2 whole months from 12/31/02 to 2/28/03. In this specific example, I think YEARFRAC gives better answers, except for the second row. In other examples, DATEDIF would do better. It’s clear to me that both options give imperfect results. I think this discussion indicates that you need to be very careful with counting months since the definition of a month varies.

Reply | Quote

Your formula is promising, but it doesn’t return the same result as DATEDIF:

Date1	Date2	DATEDIF	YEARFRAC
02/28/2003	03/29/2003	1	0

The DATEDIF formula returns the correct result. I tried setting the third argument of YEARFRAC, but couldn’t find a setting that always returns the correct value.

Reply | Quote

You could also try =INT(12*YEARFRAC(date1,date2))

Reply | Quote

DATEDIF is a strange case. It has been available in all recent versions of Excel, but it has been documented only in the online help for Excel 2000, not in Excel 97 or Excel 2002. How’s that for consistency?

See The DATEDIF Function for details.

Reply | Quote

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?

Reply | Quote

Try
=DATEDIF(startdate,enddate,”m”)

Reply | Quote

Steve gave you the answer in post 306188. 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

Reply | Quote

Format|Cells|Category:Custom|Type:mmm

Reply | Quote

Format|Cells|Category:Custom|Type:mmm

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Steve

Reply | Quote