14 Replies Latest reply on Sep 20, 2016 4:26 PM by Tom Helsley

Date Field in Microsoft Office Doc Data Card Changes On Its Own

Sally Hoo Oct 29, 2012 1:03 PM

I'm having a problem with the date field changing to the previous day every time the data card is saved. For example, if I start off with a date of 10/15/12, then if I edit another variable in the data card and save it, the date automatically changes to 10/14/12 after selecting off the file and then selecting the file again. If I were to directly change the date field itself to 9/15/12, select off the file, and then selcet the file again, the date gets changed to 9/14/12. Does anybody have any idea why the date keeps changing on its own?

Re: Date Field in Microsoft Office Doc Data Card Changes On Its Own

Ravi Teja Oct 30, 2012 3:51 AM (in response to Sally Hoo)

Hi,
I suspect this is due to the Word template you are using.You can attach the problematic template here.

Regards
Ravi T
Like Show 0 Likes(0)

Actions
- Re: Date Field in Microsoft Office Doc Data Card Changes On Its Own
  
  Sally Hoo Oct 30, 2012 10:59 AM (in response to Ravi Teja)
  
  Hi Ravi,
  
  I don't use templates and the problem occurs with Excel, Word, and PowerPoint documents.
  
  -Sally
  
  Like Show 0 Likes(0)
  
  Actions
  - Re: Date Field in Microsoft Office Doc Data Card Changes On Its Own
    
    Tim Webb Oct 30, 2012 11:03 AM (in response to Sally Hoo)
    
    Hi Sally,
    
    If you change a data card variable and save the carditeratively, will the date field keep stepping back in days? I am wondering if your date field has either logic or validation on it.
    
    Tim
    
    Like Show 0 Likes(0)
    
    Actions
    - Re: Date Field in Microsoft Office Doc Data Card Changes On Its Own
      
      Sally Hoo Oct 30, 2012 11:10 AM (in response to Tim Webb)
      
      Yes, it'll keep stepping back one day every time the data card is changed and saved. There's definitely no validation (just checked). As for logic, I tried changing the date field box to a regular edit box and mapped to the same date variable, and the problem goes away, so I don't think it's an issue with unbeknownst logic conditions either.
      
      Like Show 0 Likes(0)
      
      Actions
      - Re: Date Field in Microsoft Office Doc Data Card Changes On Its Own
        
        Jeff Sweeney Oct 30, 2012 9:10 PM (in response to Sally Hoo)
        
        Could it be your server and/or client has an incorrect system date or time zone set?
        
        Like Show 0 Likes(0)
        
        Actions
        
        Re: Date Field in Microsoft Office Doc Data Card Changes On Its Own
        
        Tim Webb Oct 30, 2012 9:12 PM (in response to Jeff Sweeney)
        
        Jeff, I think you are onto something.
        
        I had a server sync issue with the date/time a couple years ago and it caused issues like this.
        
        Like Show 0 Likes(0)
        
        Actions
        
        Re: Date Field in Microsoft Office Doc Data Card Changes On Its Own
        
        Sally Hoo Oct 31, 2012 7:13 PM (in response to Tim Webb)
        
        Both the server and my computer have the right dates.
        
        Like Show 0 Likes(0)
        
        Actions
Re: Date Field in Microsoft Office Doc Data Card Changes On Its Own

Jennifer Bahnsen Jun 6, 2013 3:44 PM (in response to Sally Hoo)

Did you ever get an answer to this problem? I'm having the same issue with an Excel file.
Like Show 0 Likes(0)

Actions
- Re: Date Field in Microsoft Office Doc Data Card Changes On Its Own
  
  Sally Hoo Jun 6, 2013 4:44 PM (in response to Jennifer Bahnsen)
  
  Nope, never did.
  
  Like Show 0 Likes(0)
  
  Actions
- Re: Date Field in Microsoft Office Doc Data Card Changes On Its Own
  
  Jason Volpe Oct 17, 2013 8:04 AM (in response to Jennifer Bahnsen)
  
  I was able to get the problem to go away by changing the EPDM variable type from Date to Text. The field in Word is also set as a date. This frustrated me for weeks!
  
  Like Show 0 Likes(0)
  
  Actions
  - Re: Date Field in Microsoft Office Doc Data Card Changes On Its Own
    
    Jeff Thomas Jul 25, 2014 9:41 AM (in response to Jason Volpe)
    
    I am having the same issue, sometimes. I have date fields set in other documents that don't change. I have tried chaging the variable it is linked to with no success. I have tried setting the variable to text and to date to no avail. Has anybody ever found a reason for the regression in the date by one day?
    
    Thanks
    
    Like Show 0 Likes(0)
    
    Actions
    - Re: Date Field in Microsoft Office Doc Data Card Changes On Its Own
      
      Tim Webb Jul 25, 2014 10:44 AM (in response to Jeff Thomas)
      
      Looks like this is a known issue in the KB, either that or Sally Hoo is the one who opened the SPR.
      
      SPR 779005 No resolution according to KB
      
      Tim CEPA
      http://www.equivaq.com
      
      Like Show 0 Likes(0)
      
      Actions
Re: Date Field in Microsoft Office Doc Data Card Changes On Its Own

Tom Helsley Sep 20, 2016 3:19 PM (in response to Sally Hoo)

In addition to SPR 779005, as mentioned by Tim Webb, SPR 647080 appears to cover the same 'wrong date' problem, except it has a possible workaround:
The workaround is to match Date format for Windows to Enterprise Vault date format.
I haven't been able to confirm this, but I think the format must exactly match, including the separation characters, not just in Windows and PDM, but also in the cell's date formatting. If someone can confirm this, please reply.
Like Show 1 Likes(1)

Actions
Re: Date Field in Microsoft Office Doc Data Card Changes On Its Own

Tom Helsley Sep 20, 2016 4:26 PM (in response to Sally Hoo)

Using a text edit box control, setting the variable type in PDM to text, and formatting the cell in Excel as text does prevent the date mismatch, but using a free-form text box sucks.

If using the date picker control, I found that changing the date variable type in PDM to text is only part of the workaround. The date picker control still seemed to change the cell format in Excel back to date and cause the custom property to 'un-link' from the named range. To get around this problem I came up with two workarounds:

To get around the un-linking problem and still use the date-picker control, I created another text edit box control for the same variable (hidden on another tab control) that was also assigned to the same date variable as the date picker control. The user could then use the date picker control to change the variable, but somehow the text edit box 'magically' kept the cell format from changing and un-linking the custom property from the named range in Excel. So far, this seems to work.

Somewhere else in the forum, I recall someone had mentioned using two variables for a similar problem. So I came up with another workaround. Use two variables on the data card... something like "DueDate" and "DueDatePicker". The "DueDate" variable type is text, and maps to the custom property in excel. The "DueDatePicker" variable doesn't map to anything, and its variable type can be date or whatever you want. On the data card, the date picker control is associated to the "DueDatePicker" variable and a text edit box is associated to the "DueDate" variable. The text edit box used an input formula that contained the variable "DueDatePicker". The rest of this goes like my first workaround. It's a few extra steps, but it might be more robust.

With both of my workarounds to use the date picker control, the date can only be set on the data card. Changing the value in Excel does not update the date picker control on the data card. So the final part of the workaround is to lock the named range cell that contains the date, and protect the worksheet.

What a PITA!
Like Show 0 Likes(0)

Actions

Go to original post