# How to extract the date text from dynamic excel range and compare with current date?

**URL:** <https://forums.eggplantsoftware.com/t/how-to-extract-the-date-text-from-dynamic-excel-range-and-compare-with-current-date/8835>\
**Category:** Eggplant Functional\
**Tags:** excel, script, sensetalk\
**Created:** [May 28, 2024, 10:50am UTC](https://forums.eggplantsoftware.com/t/how-to-extract-the-date-text-from-dynamic-excel-range-and-compare-with-current-date/8835 "2024-05-28T10:50:47Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Thanagaraj](https://forums.eggplantsoftware.com/letter_avatar_proxy/v4/letter/t/e495f1/32.png) [@Thanagaraj](https://forums.eggplantsoftware.com/u/Thanagaraj)\
**Post date:** [May 28, 2024, 10:50am UTC](https://forums.eggplantsoftware.com/t/how-to-extract-the-date-text-from-dynamic-excel-range-and-compare-with-current-date/8835/1 "2024-05-28T10:50:48Z")

</div>

Hello Team,

Conider I have the data as below in my excel.  
 ![image](https://forums.eggplantsoftware.com/uploads/default/original/1X/a972ee3beae246577c0a94a370c92c2cfe11e0e6.png)

attached example has 10 rows of data. This will change dynamically may be 8 or 15 rows.

From that i need to read the data of column values validFrom and Valid Upto

and compare with current date.

If any of the date is exceed current date logwarning message.

otherwise log success message.

---

<div class="post-metadata">

**Author:** ![Thanagaraj](https://forums.eggplantsoftware.com/letter_avatar_proxy/v4/letter/t/e495f1/32.png) [@Thanagaraj](https://forums.eggplantsoftware.com/u/Thanagaraj)\
**Post date:** [May 29, 2024, 4:00am UTC](https://forums.eggplantsoftware.com/t/how-to-extract-the-date-text-from-dynamic-excel-range-and-compare-with-current-date/8835/2 "2024-05-29T04:00:05Z")

</div>

I have used image rectangle to read the text.  
#put ReadText(iGPM\_Excel/Valid\_From\_TL, iGPM\_Excel/Valid\_From\_BL)as date into validity – failed  
put ReadText(iGPM\_Excel/Valid\_From\_TL, iGPM\_Excel/Valid\_From\_BL)as text into validity  
split validity by newline|  
log validity

output as below:  
[“01.01.2017”,“01.01.2017”,“01.10.2019”,“01.10.2019”,“01.01.2020”,“01.01.2020”,“01.09.2021”,“01.09.2021”,“01.01.2018”,“01.01.2018”,“29.02.2024”,“29.02.2024”,“30.11.2023”,“30.11.2023”,“31.01.2024”,“31.01.2024”, **“30.12.2025”** ,“31.12.2023”,“31.12.2023”,“31.12.2023”]

Now How to convert as date and compare with curernt date ?

Expected: The above list contains future date compare to current date.

Another list:  
[“01.01.2017”,“01.01.2017”,“01.10.2019”,“01.10.2019”,“01.01.2020”,“01.01.2020”,“01.09.2021”,“01.09.2021”,“01.01.2018”,“01.01.2018”,“29.02.2024”,“29.02.2024”,“30.11.2023”,“30.11.2023”,“31.01.2024”,“31.01.2024”,“30.12.2023”,“31.12.2023”,“31.12.2023”,“31.12.2023”]

Expected : The above list dates are past date.

---

<div class="post-metadata">

**Author:** ![Keiichi\_Yamamoto](https://forums.eggplantsoftware.com/letter_avatar_proxy/v4/letter/k/a8b319/32.png) [@Keiichi\_Yamamoto](https://forums.eggplantsoftware.com/u/Keiichi_Yamamoto)\
**Post date:** [May 29, 2024, 2:40pm UTC](https://forums.eggplantsoftware.com/t/how-to-extract-the-date-text-from-dynamic-excel-range-and-compare-with-current-date/8835/3 "2024-05-29T14:40:03Z")

</div>

SenseTalk has a built in functions to access Excel file programatically. Have you tried these?

> **[Working with Excel as a Database | EPF Docs](https://docs.eggplantsoftware.com/studio/stk-working-with-excel/)**
>
> You can use SenseTalk to access data stored in a Microsoft Excel (.xlsx) file as if it were a database. This means it is able to read data from a spreadsheet in the form of database records. Working with Excel files in this way allows you to use most...

> **[Excel File Interaction | EPF Docs](https://docs.eggplantsoftware.com/studio/stk-excel-file-interaction/)**
>
> You can use SenseTalk to access data stored in Microsoft Excel format. Using the functions in this section, SenseTalk reads and writes data directly in individual cell values.

I think those methods are more precise and less error prone than reading the text with OCR.

⚠ These only work if the Excel file is accessible from the Eggplant Functional. If the file resize on the SUT, then the file need to be copied from SUT or place it in a shared folder where both SUT and Eggplant Functional can access.

---

<div class="post-metadata">

**Author:** ![Karsten](https://forums.eggplantsoftware.com/letter_avatar_proxy/v4/letter/k/e47c2d/32.png) [@Karsten](https://forums.eggplantsoftware.com/u/Karsten)\
**Post date:** [May 29, 2024, 4:59pm UTC](https://forums.eggplantsoftware.com/t/how-to-extract-the-date-text-from-dynamic-excel-range-and-compare-with-current-date/8835/4 "2024-05-29T16:59:42Z")

</div>

Hey @Thanagaraj,

Absolutely agree with @Keiichi_Yamamoto.  
If you can use the excel functionality.  
If you cannot for what ever reason:

- Use the remoteclipboard. Select the text in excel, copy it to clipboard (controlkey, “c”). And put the remoteclipboards content into a variable.  
Most likely you might need to do some formatting in EPF.

```auto
## before you need to make sure the mousecursor is placed in the excel file. 
typetext Controlkey, "a" 
typetext controlkey , "c" 
put remoteclipboard(1.5) into SutClipboardContent
put SutClipboardContent

```

once you have the content with both options you can use this to check your Dates.

```auto
## adding european based Date format into sensetalk dataformat defaults
insert "[da].[mo].[year]" before the timeInputFormat ##https://docs.eggplantsoftware.com/studio/stk-global-properties-values/#sensetalk-timeinputformat
put ["01.01.2017","01.01.2017","01.10.2019","01.10.2019","01.01.2020","01.01.2020","01.09.2021","01.09.2021","01.01.2018","01.01.2018","29.02.2024","29.02.2024","30.11.2023","30.11.2023","31.01.2024","31.01.2024","30.12.2025","31.12.2023","31.12.2023","31.12.2023"] into myDateList

repeat for each Mydate in myDateList
	if date(mydate) < date("01.01.2024")
		logsuccess "date is before year 2024" && mydate
	else 
		logwarning "date is after year 2024"&& mydate
	end if
end repeat

```

Cheers;+,  
karsten

---

<div class="post-metadata">

**Author:** ![Thanagaraj](https://forums.eggplantsoftware.com/letter_avatar_proxy/v4/letter/t/e495f1/32.png) [@Thanagaraj](https://forums.eggplantsoftware.com/u/Thanagaraj)\
**Post date:** [June 4, 2024, 7:54am UTC](https://forums.eggplantsoftware.com/t/how-to-extract-the-date-text-from-dynamic-excel-range-and-compare-with-current-date/8835/5 "2024-06-04T07:54:17Z")

</div>

Thanks Karsten for code snipet.

Compare with current date:  
insert “[da].[mo].[year]” before the timeInputFormat ##[Local and Global Properties for Working with Values | EPF Docs](https://docs.eggplantsoftware.com/studio/stk-global-properties-values/#sensetalk-timeinputformat)  
put [“01.01.2017”,“01.01.2017”,“01.10.2019”,“01.10.2019”,“01.01.2020”,“01.01.2020”,“01.09.2021”,“01.09.2021”,“01.01.2018”,“01.01.2018”,“29.02.2024”,“29.02.2024”,“30.11.2023”,“30.11.2023”,“31.01.2024”,“31.01.2024”,“30.12.2025”,“31.12.2023”,“31.12.2023”,“31.12.2023”] into myDateList

repeat for each Mydate in myDateList  
if date(mydate) \< date(date)  
logsuccess “date is before year 2024” && mydate  
else  
logwarning “date is after year 2024”&& mydate  
end if  
end repeat