Network Automation Software We are currently migrating this forum
over to our HelpSystems domain. Please
post all new threads in our new
HelpSystems Community Portal.
Post to the HelpSystems Forum
You are not currently logged on. You must be logged on in order to post. Log on
Or Create a new account
AutoMate Discussion
Decrease font size
Increase font size
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
Search Topic Search Topic
Topic Tools Topic Tools
View similar topics View similar topics
View topic in raw text format. Print this topic.
Answer This question was answered by Devin Cannon, on Monday, September 11, 2017 5:14 AM

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.

 06/26/2017 09:46 PM
User is offline View Users Profile Print this message

Author Icon
ConTrelos
Apprentice (20-49)

Posts: 44
Joined: 09/01/2013

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.

 

 



 Category Survey
AutoMate 9 version: 9.0.5
Windows version: Windows 7



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
 06/26/2017 10:07 PM
User is offline View Users Profile Print this message

Author Icon
ConTrelos
Apprentice (20-49)

Posts: 44
Joined: 09/01/2013

I created a blank task and played around with the code a little.
From what I can see, the line <AMIF EXPRESSION="%ExcelDate% <= %TodaysDateRaw%"> seems to only compare the first 2 characters (Day) and ignores the rest.

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.

 



 Category Survey
AutoMate 9 version: 9.0.5
Windows version: Windows 7



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
 06/30/2017 08:27 AM
User is offline View Users Profile Print this message

Author Icon
ConTrelos
Apprentice (20-49)

Posts: 44
Joined: 09/01/2013

So many views.

Can anyone copy my code and verify if it's happening for them as well ?

 



 Category Survey
AutoMate 9 version: 9.0.5
Windows version: Windows 7
 06/30/2017 10:20 AM
User is offline View Users Profile Print this message

Author Icon
Hamid Noorbakhsh
HelpSystems

Posts: 454
Joined: 07/29/2008

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.


 Category Survey
AutoMate 9 version:
Windows version:



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
 06/30/2017 10:50 AM
User is offline View Users Profile Print this message

Author Icon
ConTrelos
Apprentice (20-49)

Posts: 44
Joined: 09/01/2013

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')%

 



 Category Survey
AutoMate 9 version: 9.0.5
Windows version: Windows 7



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
 06/30/2017 11:07 AM
User is offline View Users Profile Print this message

Author Icon
Hamid Noorbakhsh
HelpSystems

Posts: 454
Joined: 07/29/2008

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.

 Category Survey
AutoMate 9 version:
Windows version:


-------------------------
Thank you for choosing AutoMate

Hamid Noorbakhsh | Senior Software Engineer
AutoMate | A Division of HelpSystems
HelpSystems, LLC | T: +1.213.738.1700
 06/30/2017 08:13 PM
User is offline View Users Profile Print this message

Author Icon
ConTrelos
Apprentice (20-49)

Posts: 44
Joined: 09/01/2013

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



 Category Survey
AutoMate 9 version: 9.0.5
Windows version: Windows 7
 07/03/2017 08:29 AM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

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.



 Category Survey
AutoMate 9 version:
Windows version:


-------------------------

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665
 07/03/2017 06:04 PM
User is offline View Users Profile Print this message

Author Icon
ConTrelos
Apprentice (20-49)

Posts: 44
Joined: 09/01/2013

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.

Anyway, thank you once again for your assistance.



 Category Survey
AutoMate 9 version: 9.0.5
Windows version: Windows 7
 07/30/2017 08:25 PM
User is offline View Users Profile Print this message

Author Icon
ConTrelos
Apprentice (20-49)

Posts: 44
Joined: 09/01/2013

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.

 



 Category Survey
AutoMate 9 version: 9.0.5
Windows version: Windows 7



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
 07/31/2017 10:48 AM
User is offline View Users Profile Print this message

Author Icon
Justin Aucoin
HelpSystems

Posts: 360
Joined: 02/15/2016

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.

 Category Survey
AutoMate 9 version:
Windows version:



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
 08/29/2017 10:47 PM
User is offline View Users Profile Print this message

Author Icon
ConTrelos
Apprentice (20-49)

Posts: 44
Joined: 09/01/2013

Hey Justin,

  Sorry for the late reply but haven't had a chance to look at this for a while.  
Having to convert all the date formats in the excel sheet to mm/dd/yyyy would be a huge pain in the butt and would only just confuse everyone who inputs data into the sheet, so this option really isn't viable.  

  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 ?

 



 Category Survey
AutoMate 9 version: 9.0.5
Windows version: Windows 7


Edited: 08/29/2017 at 11:12 PM by ConTrelos
 08/30/2017 07:17 AM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

Originally posted by: Leonard Amabile

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.



 Category Survey
AutoMate 9 version:
Windows version:


-------------------------

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665
 08/31/2017 05:37 PM
User is offline View Users Profile Print this message

Author Icon
ConTrelos
Apprentice (20-49)

Posts: 44
Joined: 09/01/2013

Justine, 

Yes I am using cdate() like you advised earlier but it still causes errors towards the end of the month
I've attached a quick extract of my current code for your reference.

 

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



 Category Survey
AutoMate 9 version: 9.0.5
Windows version: Windows 7



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
 09/01/2017 07:21 AM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

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.




 Category Survey
AutoMate 9 version:
Windows version:


-------------------------

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665
 09/02/2017 01:02 AM
User is offline View Users Profile Print this message

Author Icon
ConTrelos
Apprentice (20-49)

Posts: 44
Joined: 09/01/2013

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.



 Category Survey
AutoMate 9 version: 9.0.5
Windows version: Windows 7


Edited: 09/02/2017 at 01:56 AM by ConTrelos
 09/05/2017 12:25 PM
User is offline View Users Profile Print this message

Author Icon
Devin Cannon
HelpSystems

Posts: 398
Joined: 03/17/2016

Answer 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.



 Category Survey



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
Statistics
18258 users are registered to the AutoMate Discussion forum.
There are currently 0 users logged in.
The most users ever online was 5551 on 01/08/2018 at 11:11 AM.
There are currently 1840 guests browsing this forum, which makes a total of 1840 users using this forum.

FuseTalk Enterprise Edition v4.0 - © 1999-2020 FuseTalk Inc. All rights reserved.

Sitemap Network Automation Software Blog