Page 4 of 5 FirstFirst ... 2 3 4 5 LastLast
Results 31 to 40 of 46

Thread: Excel Sheet Setup (Advance)

  1. #31
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0


    Register for a FREE account, and/
    or Log in to avoid these ads!

    when I input.. say Mar-13 on the LTA column for row# 10. The output skips Jan / Feb 2014 I must be misunderstanding you, because it seems to work fine on my copy. Can you upload a file, and put in some callout shapes with text or textboxes with arrows outlining exactly what the issue is?

  2. #32
    Jeff,

    Please see the attached SS and excel sheet. The highlighted cells in "Asia Pacific" are the values that are missing when it's suppose to show.

    Thanks as always, Build Test V6 Errors.xlsb

    HanClick image for larger version. 

Name:	Error SS 1.PNG 
Views:	12 
Size:	51.6 KB 
ID:	1545

    Quote Originally Posted by JeffreyWeir View Post
    when I input.. say Mar-13 on the LTA column for row# 10. The output skips Jan / Feb 2014 I must be misunderstanding you, because it seems to work fine on my copy. Can you upload a file, and put in some callout shapes with text or textboxes with arrows outlining exactly what the issue is?

  3. #33
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    I suspect this is either a date format issue or a country-specific version issue. Down here in New Zealand we use DD/MM/YY and everything displays just fine. What date format do you use, and what country are you in?

    See attached. Click image for larger version. 

Name:	Capture.PNG 
Views:	16 
Size:	16.3 KB 
ID:	1547

    Click image for larger version. 

Name:	Capture.PNG 
Views:	16 
Size:	16.3 KB 
ID:	1547

  4. #34
    I'm here in US California. It's MM/DD/YR for us I believe. But how do I tell if my excel options is using which specific date format. In the format cell inside "custom" it shows mmm-yy.

    Han

    Quote Originally Posted by JeffreyWeir View Post
    I suspect this is either a date format issue or a country-specific version issue. Down here in New Zealand we use DD/MM/YY and everything displays just fine. What date format do you use, and what country are you in?

    See attached. Click image for larger version. 

Name:	Capture.PNG 
Views:	16 
Size:	16.3 KB 
ID:	1547

    Click image for larger version. 

Name:	Capture.PNG 
Views:	16 
Size:	16.3 KB 
ID:	1547

  5. #35
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    I might have to get one of the US-based moderators to take a look, because they will be able to better see where this is falling over.

    Quick question: if you type =DATEVALUE(AF8) into the sheet, what value does it give you?

  6. #36
    it outputs "41288"

    Quote Originally Posted by JeffreyWeir View Post
    I might have to get one of the US-based moderators to take a look, because they will be able to better see where this is falling over.

    Quick question: if you type =DATEVALUE(AF8) into the sheet, what value does it give you?

  7. #37
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Ahhh. That corresponds to 14 January 2013. Mine returns 41640, which corresponds to 1 January 2014.

    Try this: reenter the dates along the top, one at a time. Don't do one then drag them across. Instead, do each one individually. So go to say AF8 and type in 01/01/2014

  8. #38
    It seems to be doing the same thing..

    Quote Originally Posted by JeffreyWeir View Post
    Ahhh. That corresponds to 14 January 2013. Mine returns 41640, which corresponds to 1 January 2014.

    Try this: reenter the dates along the top, one at a time. Don't do one then drag them across. Instead, do each one individually. So go to say AF8 and type in 01/01/2014

  9. #39
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Weird. what does =DATEVALUE(AF8) show after you manually reenter the date 1/1/2014 into AF8? And what does VALUE(AF8) show?

  10. #40
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Also, see if the issue is still happening on the attached file. Build Test V7 - dates in helper cells.xlsb.

    Apparently some weird stuff can happen to formulas where the column headers contain dates. So I put a 'helper' column in above the column headers, to see if it makes any difference.

    EDIT: I just changed my date settings to US format, and this approach seems to work fine.

    The issue is that for some obscure reason, if you enter say 1/1/2013 into a table column header, Excel stores it as a string, not a date. If you then tell excel "Apply the date format of MMM-YY to this", then Excel displays 'Jan-13' (which is what we want) but when it converts the string to a date using any of the date-related functions, Excel thinks that this number for some reason relates to the 13th of January 2013.

    Way around it is just to have some helper cells one row above. Because they are not table column headers, they are stored as dates, not strings.

    Note that you don't need these helper cells in the summary tab, because that summary table doesn't contain any formulas. Also note that no-one will see this extra helper row, because the month-by-month breakdown is only required to be visible in the summary sheet...i.e. you should hide columns T onwards in the data input sheets, like in this version. Build Test V7 - dates in helper cells_v2.xlsb
    Last edited by JeffreyWeir; 2013-08-02 at 02:55 AM.

Page 4 of 5 FirstFirst ... 2 3 4 5 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
  •