Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PC date format (M, MM vs. MMM) messes up formula...
We have over 200 Excel files on a server (for different areas of the
company) that include the formula below. Our computers, by default, have the windows date setting of M/d/yyyy (or some may have MM/dd/yyyy?). Our organization just changed policy to avoid day/month vs month/day confusion with dates, and now all PCs must have the month spelled out, such as MMM-dd-yyyy or dd-MMM-yyyy. The problem is that when a PC's system date setting is changed to MMM instead of MM or M, the following formula no longer works. The original intent of the formula is to identify all entries that were made in the current quarter, and sum up the totals {=SUM(IF(TRUNC((MONTH(A$12:A38)-1)/3,0)+1=TRUNC((MONTH(NOW())-1)/3,0)+1,F$12 :F38,0))} Where Column A has the date the entry was made, and Column F has the value being summed. I used "evaluate formula" and it comes up with #value for MONTH(A$12:A38) when the system setting is MMM, but calculates fine when using MM or M Unfortunately, the data being pushed into column A (from a userform) may have to segue over to MMM format as well for compliance to the policy, so in addition to finding a way to make the formula work when the machine system setting is changed, I need the formula to work under either system setting, and also regardless of the format of Column A. Our users are all using Excel 2003, on either Win2000 or WinXP. Any suggestions would be _greatly_ appreciated!! Thanks, Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
PC date format (M, MM vs. MMM) messes up formula...
Formatting of the cells to show the long month name won't cause your formula to
break. But text that is masquerading as a date could. If you put this in an empty cell: =count(a12:a38) do you get the same as: =counta(a12:a38) Dates are just numbers and =count() counts numbers. So if you fix those fake dates, your formula should work. KR wrote: We have over 200 Excel files on a server (for different areas of the company) that include the formula below. Our computers, by default, have the windows date setting of M/d/yyyy (or some may have MM/dd/yyyy?). Our organization just changed policy to avoid day/month vs month/day confusion with dates, and now all PCs must have the month spelled out, such as MMM-dd-yyyy or dd-MMM-yyyy. The problem is that when a PC's system date setting is changed to MMM instead of MM or M, the following formula no longer works. The original intent of the formula is to identify all entries that were made in the current quarter, and sum up the totals {=SUM(IF(TRUNC((MONTH(A$12:A38)-1)/3,0)+1=TRUNC((MONTH(NOW())-1)/3,0)+1,F$12 :F38,0))} Where Column A has the date the entry was made, and Column F has the value being summed. I used "evaluate formula" and it comes up with #value for MONTH(A$12:A38) when the system setting is MMM, but calculates fine when using MM or M Unfortunately, the data being pushed into column A (from a userform) may have to segue over to MMM format as well for compliance to the policy, so in addition to finding a way to make the formula work when the machine system setting is changed, I need the formula to work under either system setting, and also regardless of the format of Column A. Our users are all using Excel 2003, on either Win2000 or WinXP. Any suggestions would be _greatly_ appreciated!! Thanks, Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
PC date format (M, MM vs. MMM) messes up formula...
when I put a date value in the cell and used the formats you described, the
month function worked fine: 05/23/2006 5 23-May-06 5 May-23-2006 5 23-May-2006 5 the 5's were produces with =Month(A1) type formula Perhaps you just need to make sure your dates are stored as dates and not strings. -- Regards, Tom Ogilvy "KR" wrote: We have over 200 Excel files on a server (for different areas of the company) that include the formula below. Our computers, by default, have the windows date setting of M/d/yyyy (or some may have MM/dd/yyyy?). Our organization just changed policy to avoid day/month vs month/day confusion with dates, and now all PCs must have the month spelled out, such as MMM-dd-yyyy or dd-MMM-yyyy. The problem is that when a PC's system date setting is changed to MMM instead of MM or M, the following formula no longer works. The original intent of the formula is to identify all entries that were made in the current quarter, and sum up the totals {=SUM(IF(TRUNC((MONTH(A$12:A38)-1)/3,0)+1=TRUNC((MONTH(NOW())-1)/3,0)+1,F$12 :F38,0))} Where Column A has the date the entry was made, and Column F has the value being summed. I used "evaluate formula" and it comes up with #value for MONTH(A$12:A38) when the system setting is MMM, but calculates fine when using MM or M Unfortunately, the data being pushed into column A (from a userform) may have to segue over to MMM format as well for compliance to the policy, so in addition to finding a way to make the formula work when the machine system setting is changed, I need the formula to work under either system setting, and also regardless of the format of Column A. Our users are all using Excel 2003, on either Win2000 or WinXP. Any suggestions would be _greatly_ appreciated!! Thanks, Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
PC date format (M, MM vs. MMM) messes up formula...
I have a monthview calendar control that pops up from a userform, with the
code: TempDate = MonthView1.Value Then that information gets written to the spreadsheet via: Sheet1.Range("A13").Value = frmEntry.lblD2 since it is pulling this value from the label (DOH!) it is coming in as text- I just hadn't picked up on that, since the M and MM formulas were able to function off the text string. Now I've got to figure out how to either (a) cycle all the workbooks and change them all to dates, and fix the code in each workbook so it won't keep happening, or, (b) come up with some way to pull what I need from the text strings. Either way, I think I'm in for some pain. :-/ Thanks Tom & Dave, for pointing out the text issue. Best, Keith "Tom Ogilvy" wrote in message ... when I put a date value in the cell and used the formats you described, the month function worked fine: 05/23/2006 5 23-May-06 5 May-23-2006 5 23-May-2006 5 the 5's were produces with =Month(A1) type formula Perhaps you just need to make sure your dates are stored as dates and not strings. -- Regards, Tom Ogilvy "KR" wrote: We have over 200 Excel files on a server (for different areas of the company) that include the formula below. Our computers, by default, have the windows date setting of M/d/yyyy (or some may have MM/dd/yyyy?). Our organization just changed policy to avoid day/month vs month/day confusion with dates, and now all PCs must have the month spelled out, such as MMM-dd-yyyy or dd-MMM-yyyy. The problem is that when a PC's system date setting is changed to MMM instead of MM or M, the following formula no longer works. The original intent of the formula is to identify all entries that were made in the current quarter, and sum up the totals {=SUM(IF(TRUNC((MONTH(A$12:A38)-1)/3,0)+1=TRUNC((MONTH(NOW())-1)/3,0)+1,F$12 :F38,0))} Where Column A has the date the entry was made, and Column F has the value being summed. I used "evaluate formula" and it comes up with #value for MONTH(A$12:A38) when the system setting is MMM, but calculates fine when using MM or M Unfortunately, the data being pushed into column A (from a userform) may have to segue over to MMM format as well for compliance to the policy, so in addition to finding a way to make the formula work when the machine system setting is changed, I need the formula to work under either system setting, and also regardless of the format of Column A. Our users are all using Excel 2003, on either Win2000 or WinXP. Any suggestions would be _greatly_ appreciated!! Thanks, Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort messes up pictures | Excel Discussion (Misc queries) | |||
link messes with text format | Excel Discussion (Misc queries) | |||
auto-filter messes up the formulas | Excel Discussion (Misc queries) | |||
saving messes up my command buttons | Excel Programming | |||
I need today's date returned as date format in formula | Excel Discussion (Misc queries) |