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)
<AMLABEL LABELNAME="Todays Date" /> <AMVARIABLE NAME="TodaysDateRaw">%Now()%</AMVARIABLE> <AMTEXTFORMATDATETIME DATETIMEFORMAT="dd/mm/yyyy" RESULTVARIABLE="TodaysDateRaw" /> <AMLABEL LABELNAME="Open Excel Sheet" /> <AMEXCELOPENWORKBOOK WORKBOOK="D:\datasheet.xlsx" /> <AMVARIABLE NAME="ExcelDate"></AMVARIABLE> <AMVARIABLE NAME="CompanyName"></AMVARIABLE> <AMLOOP TOTALLOOPS="20"> <AMEXCELGETCELL RESULTVARIABLE="ExcelDate" CELLREF="A1" /> <AMSET VARIABLENAME="ExcelDate">%DateValue(ExcelDate)%</AMSET> <AMSET VARIABLENAME="ExcelDate">%DateAdd( &amp;amp;quot;d&amp;amp;quot;, 1, CStr(ExcelDate))%</AMSET> <AMSET VARIABLENAME="ExcelDate">%Format(ExcelDate, 'dd/mm/yyyy')%</AMSET> <AMIF EXPRESSION="%ExcelDate% &amp;amp;lt;= %TodaysDateRaw%"> <AMEXCELGETCELL RESULTVARIABLE="CompanyName" CELLREF="D1" /> <AMGOTO LABELNAME="%CompanyName%" /> <AMELSE /> <AMGOTO LABELNAME="End" /> <AMLABEL LABELNAME="End" /> <AMEXCELCLOSEWORKBOOK />

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)
<AMLABEL LABELNAME="Todays Date" /> <AMVARIABLE NAME="TodaysDateRaw">%Now()%</AMVARIABLE> <AMTEXTFORMATDATETIME DATETIMEFORMAT="dd/mm/yyyy" RESULTVARIABLE="TodaysDateRaw" /> <AMLABEL LABELNAME="Open Excel Sheet" /> <AMEXCELOPENWORKBOOK WORKBOOK="D:\Kosta\Desktop\TVSchedule.xlsx" /> <AMWINDOW ACTIVITY="wait" WINDOWTITLE="TVSchedule*" /> <AMVARIABLE NAME="ExcelDate"></AMVARIABLE> <AMVARIABLE NAME="ShowTitle"></AMVARIABLE> <AMVARIABLE NAME="ValueVal"></AMVARIABLE> <AMEXCELGETCELL RESULTVARIABLE="ExcelDate" CELLREF="A1" /> <AMSET VARIABLENAME="ExcelDate">%DateValue(ExcelDate)%</AMSET> <AMSET VARIABLENAME="ExcelDate">%DateAdd( &quot;d&quot;, 1, CStr(ExcelDate))%</AMSET> <AMSET VARIABLENAME="ExcelDate">%Format(ExcelDate, 'dd/mm/yyyy')%</AMSET> <AMIF EXPRESSION="%ExcelDate% &lt;= %TodaysDateRaw%"> <AMSET VARIABLENAME="ValueVal">True</AMSET> <AMELSE /> <AMSET VARIABLENAME="ValueVal">False </AMSET> </AMIF>

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

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)
<AMVARIABLE NAME="A">%Date()%</AMVARIABLE> <AMVARIABLE NAME="B">%DateAdd(&quot;d&quot;, 1, Date())%</AMVARIABLE> <AMSHOWDIALOG>%A%</AMSHOWDIALOG> <AMSHOWDIALOG>%B%</AMSHOWDIALOG> <AMIF EXPRESSION="%A% &lt;= %B%"> <AMSHOWDIALOG>%A%</AMSHOWDIALOG> <AMELSE /> <AMSHOWDIALOG>%B%</AMSHOWDIALOG> </AMIF>

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

 

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)
<AMVARIABLE NAME="A">%Date()%</AMVARIABLE> <AMVARIABLE NAME="B"></AMVARIABLE> <AMEXCELOPENWORKBOOK WORKBOOK="D:\Kosta\Desktop\Book2.xlsx" /> <AMEXCELGETCELL RESULTVARIABLE="B" CELLREF="A1" /> <AMSHOWDIALOG>%A%</AMSHOWDIALOG> <AMSHOWDIALOG>%B%</AMSHOWDIALOG> <AMIF EXPRESSION="%A% &amp;amp;lt;= %B%"> <AMSHOWDIALOG>%A%</AMSHOWDIALOG> <AMELSE /> <AMSHOWDIALOG>%B%</AMSHOWDIALOG> </AMIF>

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.

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

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

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

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

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)
<AMVARIABLE NAME="A">%Date()%</AMVARIABLE> <AMVARIABLE NAME="B">%DateAdd(&quot;d&quot;, 1, Date())%</AMVARIABLE> <AMSHOWDIALOG>%A%</AMSHOWDIALOG> <AMSHOWDIALOG>%B%</AMSHOWDIALOG> <AMIF EXPRESSION="%A% &lt;= %B%"> <AMSHOWDIALOG>%A%</AMSHOWDIALOG> <AMELSE /> <AMSHOWDIALOG>%B%</AMSHOWDIALOG> </AMIF>

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)
<AMVARIABLE NAME="A"></AMVARIABLE> <AMVARIABLE NAME="B"></AMVARIABLE> <AMTEXTFORMATDATETIME DATETIMEFORMAT="mm/dd/yyyy" RESULTVARIABLE="A" /> <AMTEXTFORMATDATETIME DATETIMEFORMAT="mm/dd/yyyy" RESULTVARIABLE="B" ADJUSTDAY="1" /> <AMSHOWDIALOG>%A%</AMSHOWDIALOG> <AMSHOWDIALOG>%B%</AMSHOWDIALOG> <AMIF EXPRESSION="%A% &lt;= %B%"> <AMSHOWDIALOG>%A%</AMSHOWDIALOG> <AMELSE /> <AMSHOWDIALOG>%B%</AMSHOWDIALOG> </AMIF>

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

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

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

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)
<AMLABEL LABELNAME="Todays Date" /> <AMVARIABLE NAME="TodaysDateRaw">%Now()%</AMVARIABLE> <AMTEXTFORMATDATETIME DATETIMEFORMAT="dd/mm/yyyy" TWELVEHOURFORMAT="NO" RESULTVARIABLE="TodaysDateRaw" /> <AMLABEL LABELNAME="Open Excel Sheet" /> <AMEXCELOPENWORKBOOK WORKBOOK="C:\Schedule.xlsx" /> <AMWINDOW ACTIVITY="wait" WINDOWTITLE="TVSchedule*" /> <AMVARIABLE NAME="ExcelDate"></AMVARIABLE> <AMVARIABLE NAME="ShowTitle"></AMVARIABLE> <AMEXCELGETCELL RESULTVARIABLE="ExcelDate" CELLREF="A1" /> <AMSET VARIABLENAME="ExcelDate">%cdate(ExcelDate)%</AMSET> <AMTEXTFORMATDATETIME DATETIMEFORMAT="dd/mm/yyyy" RESULTVARIABLE="ExcelDate" ACTION="specified" SPECIFIEDDATETIME="%ExcelDate%" SPECIFIEDFORMAT="dd/mm/yyyy" ADJUSTDAY="1" /> <AMIF EXPRESSION="%ExcelDate% &lt;= %TodaysDateRaw%">

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

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

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)
<AMIF EXPRESSION="%DateDiff(&amp;quot;d&amp;quot;,&amp;quot;1/1/1900&amp;quot;,ExcelDate)% &amp;lt;= %DateDiff(&amp;quot;d&amp;quot;,&amp;quot;1/1/1900&amp;quot;,TodaysDateRaw)%">

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

Devin Cannon | Support Analyst
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1.213.738.1700 | F: +1.213.738.7665