![]() |
![]() |
We are currently migrating this forum over to our HelpSystems domain. Please post all new threads in our new HelpSystems Community Portal. |
![]() |
|
![]() |
AutoMate Discussion | ![]() |
help :
faq :
home
|
||
Latest News:
|
latest topics : statistics |
Topic Title: Possible Bug found Topic Summary: Function "Is Less Than or Equal To" isn't working properly Created On: 06/26/2017 09:46 PM Status: Read Only |
Linear : Threading : Single : Branch |
![]() |
![]()
|
![]() Answer: Hi, Excel will use your date format defined in your Region settings in Windows. From what I'm seeing, the AutoMate IF action does not compare dates based on Windows settings but will properly compare yyyy/MM/dd and MM/dd/yyyy formats. When trying to compare dd/MM/yyyy dates, AutoMate will still see this as MM/dd/yyyy even if your Region settings are set otherwise. I'll put in a bug/feature request to have the date format pulled from Windows. If you don't wish to change the format of your dates, another option would be to calculate the date difference from 1/1/1900 using the "DateDiff()" function (which WILL use your Windows Region date format) and then compare those values. Please see the attached code below. | ||||||||||
![]() |
||||||||||
Hey guys. Not sure if there is an error with the code orif this is a huge bug in the software. I have created an IF statement which is suppose to look at 2 dates (TodaysDateRaw and ExcelDate). I specified IF ExcelDate is less than or equal to TodaysDateRaw, to get some information out of Excel, otherwise to just end the task. For Example, if ExcelDate = 20/07/2017, and TodaysDateRaw = 27/06/2017, this should logically be false, so it would just jump to the ELSE statement....... but it doesn't. It continues to process the task as if it were a true statement.
Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder) |
||||||||||
|
||||||||||
![]() |
||||||||||
I created a blank task and played around with the code a little. So, in AutoMate's view, given the task: ExcelDate = 30/06/2017 TodaysDate = 27/06/2017 IF ExcelDate <= TodaysDate..... then it gets processed as TRUE because 30 is greater than 27. I've attached asimple code that you can copy and paste into a new Task to test for yourself. Create an Excel file on your c:\ and play around with the dates in cell A1 You'll find that AutoMate only compares the DAY, not the entire Date.
Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder) |
||||||||||
|
||||||||||
![]() |
||||||||||
So many views. Can anyone copy my code and verify if it's happening for them as well ?
|
||||||||||
|
||||||||||
![]() |
||||||||||
ConTrelos,
I have created a small task and it runs fine. You might be aware but comparison of dates is done in time passed with respect to a fixed previous date (usually January 1st 1900), so Today's Date will be 'Greater' than Yesterday's Date. Please put MessageBoxes in your code so you could see the exact things you are comparing, it might just be a format issue and the way your computer is handling time and date formatting. Hope this helps.
Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder) ------------------------- Thank you for choosing AutoMate Hamid Noorbakhsh | Senior Software Engineer AutoMate | A Division of HelpSystems HelpSystems, LLC | T: +1.213.738.1700 |
||||||||||
|
||||||||||
![]() |
||||||||||
Hi Hamid I have tried your code and it works if I specify the date in the variables. Now, I've made a slight modification to your code and it fails. Create an Excel sheet on your desktop with the date 10/06/2017 in cell A1 Modify the code to point to your excel file
Given that the 10/6/2017 is before todays date, the dialog box should show me the 10/6/2017...............but it doesn't.
I suspect the bug is in the way your software picks up the value from within cells in Excel. In my original code, I even went the extra step to made sure that Automate recognised the value in cell A1 as being a date with the line %Format(ExcelDate, 'dd/mm/yyyy')%
Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder) Edited: 06/30/2017 at 07:37 PM by ConTrelos |
||||||||||
|
||||||||||
![]() |
||||||||||
ConTrelos,
I do not have Excel installed so I can't immediately test your issue, but I am assuming it might have to with dateformats. I also noticed that your AMIF Expression is coded slightly different, just as an observation, your is escaped &s;lt; I will have this test, but might be in a few days after US holidays here.
------------------------- Thank you for choosing AutoMate Hamid Noorbakhsh | Senior Software Engineer AutoMate | A Division of HelpSystems HelpSystems, LLC | T: +1.213.738.1700 |
||||||||||
|
||||||||||
![]() |
||||||||||
Hamid, The IF Statement might be a little different but it essentially does the same thing. I'm not sure where the issue would be. I even tried formatting the cell in excel to date format dd/mm/yyyy
|
||||||||||
|
||||||||||
![]() |
||||||||||
ConTrelos,
If you were comparing 2 dates within Excel then your scenario will work as expected. However, Automate is not aware that the string you are providing is a date or a numerical string. 10/06/2017 is lesser than 12/07/2016 from Automate's point of view because Automate is comparing strings. You either need to format the strings to be YYYYMMDD or use the cdate() function.
------------------------- ![]() Leonard Amabile | Director, Cross Platform Support AutoMate | InterMapper | Skybot | Divisions of HelpSystems T: +1.213.738.1700 | F: +1.213.738.7665 |
||||||||||
|
||||||||||
![]() |
||||||||||
Thank You for that suggestion. cdate() worked like a charm. I thought I had that already covered since I added the line Format(), but ovbiously that wasn't enough.
The part that had me confused was in the Variables Tab (In the Task Builder), the ExcelDate was coming up in it's proper date format. Had it been showing it as a string, I would had picked up on the error and found a way to fix it.
|
||||||||||
|
||||||||||
![]() |
||||||||||
Hey guys Back with the same problem again. Automate was working great for a while until today when it started playing up again. I thought I would go back to basics and see where the error was happening.....which lead me allll the way back to this thread. It is currently the 31/07/2017. I copied and pasted Hamid's initial code (code attached) into automate and it failed. It recognises todays date as being 31/07/2017 (Variable A). It also successfully added 1 day which made Variable B as the 01/08/2017. BUT..... the IF Statement doesn't work properly. Instead of showing the lesser date (Var A) ,it shows me Var B.
Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder) |
||||||||||
|
||||||||||
![]() |
||||||||||
Hi ConTrelos,
This is due the International date formatting and is a known issue in your release. The current releases will correctly recognize the International date formatting and the IF statement will work correctly. For version 9, you will need to use the mm/dd/yyyy formatting instead. This can be done using the Text Formate date/time actions such as in the attached step below. Another alternative would be to to have a nested if statement to compare the months first, and then the days.
Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder) ------------------------- ![]() Justin Aucoin | Support Analyst AutoMate | Division of HelpSystems HelpSystems, LLC | T: +1.213.738.1700 | F: +1.213.738.7665 |
||||||||||
|
||||||||||
![]() |
||||||||||
Hey Justin, Sorry for the late reply but haven't had a chance to look at this for a while. You mentioned nested IF statements to compare the months first. How would I go about doing that using the current date format dd/mm/yyyy ?
Edited: 08/29/2017 at 11:12 PM by ConTrelos |
||||||||||
|
||||||||||
![]() |
||||||||||
ConTrelos, If you were comparing 2 dates within Excel then your scenario will work as expected. However, Automate is not aware that the string you are providing is a date or a numerical string. 10/06/2017 is lesser than 12/07/2016 from Automate's point of view because Automate is comparing strings. You either need to format the strings to be YYYYMMDD or use the cdate() function. ConTrelos, Are you not using the cdate() function as explained in my previous post? This will always compare the dates correctly.
------------------------- ![]() Leonard Amabile | Director, Cross Platform Support AutoMate | InterMapper | Skybot | Divisions of HelpSystems T: +1.213.738.1700 | F: +1.213.738.7665 |
||||||||||
|
||||||||||
![]() |
||||||||||
Justine, Yes I am using cdate() like you advised earlier but it still causes errors towards the end of the month
Given that both the "todaysdateraw" and "ExcelDate" are set to dd/mm/yyyy formats, I would assume that it should work properly, but for some strange reason it doesn't. If you put 30/08/2017 into Excel and the current date as 1/09/2017, the IF statement still fails, even though ExcelDate <= TodaysDateRaw
Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder) |
||||||||||
|
||||||||||
![]() |
||||||||||
ConTrelos,
The IF statement does not know and does not care if the string is a date. It is looking at the date as a numeric value. 08/31/2017 > 08/31/2016 - 08312017 is greater than 08312016 08/31/2017 < 09/01/2016 - 08312017 is less than 09012016 Do you see the difference? In order to compare dates properly you need to change their format to YYYYMMDD. The 2 above examples again: 20170831 > 20160831 20170831 > 20160901 Once you have changed the format, the IF statement will always be accurate.
------------------------- ![]() Leonard Amabile | Director, Cross Platform Support AutoMate | InterMapper | Skybot | Divisions of HelpSystems T: +1.213.738.1700 | F: +1.213.738.7665 |
||||||||||
|
||||||||||
![]() |
||||||||||
I changed the date formats as you suggest to YYYY/MM/DD I've also changed the Excel Sheet to reflect your recommendation as well (yyyy/mm/dd) This now produces an error on Step 13 (Date/Time Format incorrect)
After a lot of frustration and a lot more swearing, I managed to find a work around this date issue with absolutely no thanks to Automate (the software). It requires an extra step when in Excel but at least it works properly now. Cell A1 "=Today()" in date format dd/mm/yyyy Cell B1"=A1" in General Number format (as of 2/09/2017 = 42980 which is 42980 days after 01/01/1900) The rest of the data starts from A3 downwards using the above formatting. TodaysDateRaw now takes the value from B1 ExcelDate now takes value from B3. Now the IF statement works properly.
Just an FYI, Australia, Europe, South Africa, South America, and most of Canada use the format DD/MM/YYYY Most of Asia, including China & Japan is the only part of the world to use YYYY/MM/DD The USA is the only country to use MM/DD/YYYY. Perhaps the Automate Devs/programmers can take some inspiration from Excel in regards to how it handles dates. Especially seeing as how it promotes itself as a fully automated software...... but somehow can't compare 2 dates according to each region's date formatting ? It would take no time to do something similar. Just add an extra line of code to calculate how many days the Inputed date is from 01/01/1900 and then compare that value in the background (like excel does). That way it would also be compatible with Office documents.
Edited: 09/02/2017 at 01:56 AM by ConTrelos |
||||||||||
|
||||||||||
![]() |
||||||||||
Hi, Excel will use your date format defined in your Region settings in Windows. From what I'm seeing, the AutoMate IF action does not compare dates based on Windows settings but will properly compare yyyy/MM/dd and MM/dd/yyyy formats. When trying to compare dd/MM/yyyy dates, AutoMate will still see this as MM/dd/yyyy even if your Region settings are set otherwise. I'll put in a bug/feature request to have the date format pulled from Windows. If you don't wish to change the format of your dates, another option would be to calculate the date difference from 1/1/1900 using the "DateDiff()" function (which WILL use your Windows Region date format) and then compare those values. Please see the attached code below.
Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder) ------------------------- ![]() Devin Cannon | Support Analyst AutoMate | Division of HelpSystems HelpSystems, LLC | T: +1.213.738.1700 | F: +1.213.738.7665 |
||||||||||
|
AutoMate Discussion
» AUTOMATE » AutoMate 9
»
Possible Bug found
|
![]() |
FuseTalk Enterprise Edition v4.0 - © 1999-2021 FuseTalk Inc. All rights reserved.