Start a new topic

2021-11-09T12:05:19 UTC Date format issue

Hi


I am hoping you can shed some light on this please.


2021-11-09T12:05:19 UTC - this a date from a downloaded spreadsheet. I have tried everything on importomatic, from its dictionaries to writing a vba code for importomatic. I still get an invalid error date for that particular date. What is the best way to change that format into dd/mm/yyyy please?


Thank you 


Best Regards


Rej 


Hello Rej, can you confirm what the desired date needed from your example should be? Is the month represented by the '11' or would that be the '09' for you? We have a regular expression dictionary that may do exactly what you need, just please confirm what the correct desired value should be from your given example - many thanks!

Hello 

2021-11-09T12:05:19 UTC  needs to change into this 09/11/2021 please - need to remove time stamp too. 

Hope it helps?


Thank you! 






1 person likes this

Please see screenshots and/or the attached excel file for UTC to dd/mm/yyyy formatting with a regular expression dictionary: 

image

Let us know if applying this doesn't seem to work, be sure to check the 'RegEx' checkbox on the new dictionary before saving it and applying on your profiles. 

csv
(111 Bytes)

image 1



image



image

I use a dictionary test and now you can see the screenshot where BLANK is visible.

Regards

Rej - Try hitting 'delete' on your keyboard when highlighting the --BLANK-- entry above and allow it to be removed, then try to re-add it to the dictionary again manually before saving. If that 'Blank' still doesn't test as it should, it may be a good idea to try adding the RegEx formulas again to a brand new empty dictionary, which I find usually resolves that '--BLANK--' issue. I find this sometimes occurs when altering an existing dictionary entry instead of creating a brand new one - let us know if that seems to help!

A new clean dictionary can be created by clicking Add, then "No" when prompted to select from a table:


Click 'No' for a Brand New Blank Dictionary


That should resolve the visible '--BLANK--' value you are seeing in dictionary testing, which seems to mainly occur when attempting to update an existing dictionary with a blank value instead of creating a brand new one, hope that helps!


1 person likes this

It will not let me to delete highlighting the --BLANK-- at all.  Please see the screenshot below.

Regards


image


(^\d{4})-((?<=-)\d{2}(?=-))-(\d{2})\S* can you confirm this is correct?

Have created a brand new one and for that the result is still the same. 



Hi 


I just want to say thank you for your time and your help on this matter even though the issue is still not working my end. 


It is strange that importomatic dictionaries cannot find a way to solve the issue. 


Out of curiosity, have you ever come across this inside the VBA  in importomatic? Would that be too much of an investment of time to search for a solution? 


Thanks 


Regards

Hello Rej, I'm sorry that dictionary formula is not behaving for you, I have just tested it again to ensure it works on my end using your exact date and have re-exported it for you below. Are you copying/pasting the values into a new blank dictionary from my spreadsheet or typing the entries in by hand? In either case, you may want to double check to ensure that no additional spaces are appearing at the end of those formulas, and that both have the 'RegEx' box checked in the lower-left corner of the dictionary. I don't know of any VBA solutions for this particular date issue, but would be interested in learning more if you happen to find something that you'd be willing to share. Here is a recently updated KB article on how to import from an excel spreadsheet to a new dictionary in case it helps: How to Import a Dictionary : (omaticsoftware.com)

csv
(111 Bytes)

Hello

 

I am pleased the format for the date 2021-11-09T12:05:19 UTC has been resolved.

 

The solution my end:

I created it manually to type BLANK, which didn’t work. Instead I just clicked the plus button and then it automatically brings up BLANK itself.




image.png





Also I missed out the 'd' under the BLANK and I have corrected it using your spreadsheet which is very helpful to identify what does not match.

 

Again I am so pleased with it and thank you so much for your kind help on this matter which is greatly appreciated.

 

Regards

 

Rej  

Login or Signup to post a comment