0

date #value issues with international users

Joanne Lebiecki

February 13, 2018 14:30

None

Follow

Hello, 

I am a user located in the U.S and I have created a few financial statement Jet Reports for our divisions to use. Our domestic U.S. users have no problems with the report, however, some of our European and Asian users get #value displaying instead of the date in column headers. When filling in the options of the report, I have asked all users to fill in mm/dd/yy. The rest of the report appears to work appropriately and pull in the account balances for the dates provided, but the column headers don't reflect the dates. Furthermore, sometimes when they send the file to me, the dates will show correctly for me, but they only see #value. 

I assume it might have to do with the different date settings in the U.S. mm/dd/yy (as they are entered in the reports) compared to their local computers being in dd/mm/yy format? Does anyone have any help on how to solve this? Is there a setting that needs to be changed? 

Thank you in advance!

Joanne 

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Harry Lewis February 13, 2018 20:54

  When sharing Excel workbooks between countries, dates can be a challenge.

  One option is to ensure that you have not hard-coded dates in function calls:

  =IF(F6<"12/31/2010",1,0)

  or attempt to enforce a particular format:

  =IF(F6<>TEXT(D7,"MM/DD/YYYY"),1,0)

   

  The best option is to place dates in cells and then reference those cells:

  =IF(F6<$C$3,1,0)

  and ensure that the Excel format used for those cells is one that automatically responds to regional date settings:

  

  in this way, you can put in a date such as 12/15/2018 and your remote user can enter 15/12/2018... and you both the the results you are expecting.

   

  I hope that helps

• 0
  
  Joanne Lebiecki February 14, 2018 16:21

  Thank you for your suggestions. I have evaluated some of my formulas, and while I don't have any specific dates hard coded, I do have several formulas using day/month references as seen below: 

  =EOMONTH(E$2,0)

  =DATE(YEAR(F2)-1,MONTH(F2),DAY(F2))

  Is there really any way around this? 

  Thanks,

  Joanne 

• 0
  
  Harry Lewis February 14, 2018 16:45

  Hi Joanne -

  As long as the cells containing those functions are formatted using one of Excel's auto-adjusting date format, they shouldn't represent an issue.

  You can try duplicating what your users are seeing by changing your system settings to one that uses a different date format.

  For example:

  If you are still unable to resolve what your users are experiencing, you would probably want to submit a support request so that you could send in your report for suggestions.