Unable to compare excel date output to today date and count values

[anand_kumar4 (anand kumar)]

Hi ,
i have an excel data format like–>2/12/2025 .

Activity ID	orderdate
32	2/12/2025
4	1/12/2025
5	11/12/2025
7	9/12/2025
8	16/12/2025
9	2/1/2026
1	3/1/2026
2	14/01/2026
4	15/01/2026
12	18/01/2026
11	19/01/2026
10	22/01/2026

when i am reading and loop datatable the output date format getting different format like below

i am unable to compare today date and excel data and count feature vales and past values with today date.
new data - Copy.xlsx (8.6 KB)

[prashant1603765 (Prashant Kumar)]

Hi @anand_kumar4

In for each loop assign like below… pls modify variables accordingly if required…

orderDate = DateTime.ParseExact(row(“orderdate”).ToString,
{“d/M/yyyy”,“dd/MM/yyyy”},
System.Globalization.CultureInfo.InvariantCulture,
Globalization.DateTimeStyles.None).Date
todayDate = Now.Date

Then u can use If loop then count…

orderDate < todayDate
orderDate = todayDate
orderDate > todayDate

Happy Automation

[Monali_Vekariya (Mona)]

Hi @anand_kumar4

Step 1: Read Excel

Use Read Range → store in dtExcel

Step 2: For Each Row

Use For Each Row in dtExcel

Step 3: Convert Excel date

Inside loop, use Assign:

excelDate = DateTime.Parse(row(“orderdate”).ToString)

(Variable type: DateTime)

Step 4: Compare with today

If excelDate.Date < DateTime.Today Then
“Past date”
ElseIf excelDate.Date = DateTime.Today Then
“Today date”
Else
“Future date”
End If

[MohammedShabbir (shabbir.qs)]

Excel dates are read in mixed formats ,so convert the value explicitly using DateTime.ParseExact with patterns (d/M/yyyy, dd/MM/yyyy) before comparing with Date.Today.

You want to compare this in excel in new column or are you processing after reading as datatable. Will help accordingly.

[anand_kumar4 (anand kumar)]

Hi Prashanth , i used your code but i am getting below error in my workflow

[anand_kumar4 (anand kumar)]

hi, after reading excel , i want to compare excel data (orderdate) with today date .
problem with dateformat after reading date

[nishiijain2000 (Nishi Jain)]

Hi @anand_kumar4

Try this
orderDateDT (DateTime)
Assign this
orderDateDT = DateTime.ParseExact(
CurrentRow(“orderdate”).ToString.Trim,
“dd/MM/yyyy”,
System.Globalization.CultureInfo.InvariantCulture
)
Then use if activity
orderDateDT.Date = DateTime.Today

Hope it help

[anand_kumar4 (anand kumar)]

hi, i used your steps but i am getting below error date format issue

[vhvinodsai (Vinod harapanahalli)]

Hi @anand_kumar4

This issue can be resolved by using linq function please find below :
Use assign activity inside
countToday = dtExcel.AsEnumerable().
Count(Function(r) DateTime.FromOADate(CDbl(r(“Excelcolumnname”))).Date = Now.Date)

Hope it works for you!

Happy Automating:)

[Monali_Vekariya (Mona)]

Hi @anand_kumar4

Step 1: Create variable

Create variable:

• Name: excelDate
• Type: DateTime

Step 2: Inside For Each Row

Add Assign activity:

excelDate = DateTime.ParseExact(
row(“orderdate”).ToString.Trim,
{“d/M/yyyy”,“dd/MM/yyyy”},
System.Globalization.CultureInfo.InvariantCulture,
System.Globalization.DateTimeStyles.None
)

Step 3: Compare with today

If excelDate.Date < DateTime.Today Then
’ Past date
ElseIf excelDate.Date = DateTime.Today Then
’ Today date
Else
’ Future date
End If

[Anil_G (Anil Gorthi)]

@anand_kumar4

read data into datatble use below

dt.AsEnumerable.Where(function(x) Cdate(x("orderdate").ToString) > Now).Count Date > Today
dt.AsEnumerable.Where(function(x) Cdate(x("orderdate").ToString) < Now).Count Date < Today

cheers

[prashant1603765 (Prashant Kumar)]

Hi @anand_kumar4

Now it’ll 100% work - Just small change required:

Under For each row- use assign activity-
orderdate = If(TypeOf row(“orderdate”) Is DateTime, CType(row(“orderdate”), DateTime).Date, DateTime.ParseExact(row(“orderdate”).ToString, {“dd/MM/yyyy”,“d/M/yyyy”,“dd-MM-yyyy”,“d-M-yyyy”}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None).Date)

Then you can cross check via writeline-
If(orderDate < todayDate,
"Past: " & row(“Activity ID”).ToString(),
If(orderDate = todayDate,
"Today: " & row(“Activity ID”).ToString(),
"Future: " & row(“Activity ID”).ToString()
)
)

If helpful, mark as solution. Happy automation with UiPath

[prashant1603765 (Prashant Kumar)]

Ref-

Or if you want just count you can use writeline after for each - with below expression

"Past: " & dtNew.AsEnumerable().Count(Function(r) If(TypeOf r(“orderdate”) Is DateTime, CType(r(“orderdate”), DateTime).Date, DateTime.ParseExact(r(“orderdate”).ToString, {“dd/MM/yyyy”,“d/M/yyyy”,“dd-MM-yyyy”,“d-M-yyyy”}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None).Date) < todayDate) &
" | Today: " & dtNew.AsEnumerable().Count(Function(r) If(TypeOf r(“orderdate”) Is DateTime, CType(r(“orderdate”), DateTime).Date, DateTime.ParseExact(r(“orderdate”).ToString, {“dd/MM/yyyy”,“d/M/yyyy”,“dd-MM-yyyy”,“d-M-yyyy”}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None).Date) = todayDate) &
" | Future: " & dtNew.AsEnumerable().Count(Function(r) If(TypeOf r(“orderdate”) Is DateTime, CType(r(“orderdate”), DateTime).Date, DateTime.ParseExact(r(“orderdate”).ToString, {“dd/MM/yyyy”,“d/M/yyyy”,“dd-MM-yyyy”,“d-M-yyyy”}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None).Date) > todayDate)

Happy Automation

[Parvathy (PS Parvathy)]

Hi @anand_kumar4

=> Read Range Workbook. Make Use Display format as True
Output → dt

=> Use below syntax to get Past Count and Future count
PastCount = dt.AsEnumerable().Where(Function(x) If(TypeOf x("orderdate") Is Date, DirectCast(x("orderdate"), Date).Date, DateTime.Parse(x("orderdate").ToString(), System.Globalization.CultureInfo.CreateSpecificCulture("en-GB"), System.Globalization.DateTimeStyles.AssumeLocal).Date) < DateTime.Today).Count()

FutureCount = dt.AsEnumerable().Where(Function(x) If(TypeOf x("orderdate") Is Date, DirectCast(x("orderdate"), Date).Date, DateTime.Parse(x("orderdate").ToString(), System.Globalization.CultureInfo.CreateSpecificCulture("en-GB"), System.Globalization.DateTimeStyles.AssumeLocal).Date) > DateTime.Today).Count()

PastCount and FutureCount is of DataType System.Int32

If you want Past Count and Future Count in a single syntax use the below one:
rowCount = "Past Count: " + dt.AsEnumerable().Where(Function(x) If(TypeOf x("orderdate") Is Date, DirectCast(x("orderdate"), Date).Date, DateTime.Parse(x("orderdate").ToString(), System.Globalization.CultureInfo.CreateSpecificCulture("en-GB"), System.Globalization.DateTimeStyles.AssumeLocal).Date) < DateTime.Today).Count().ToString() + "| Future Count: " + dt.AsEnumerable().Where(Function(x) If(TypeOf x("orderdate") Is Date, DirectCast(x("orderdate"), Date).Date, DateTime.Parse(x("orderdate").ToString(), System.Globalization.CultureInfo.CreateSpecificCulture("en-GB"), System.Globalization.DateTimeStyles.AssumeLocal).Date) > DateTime.Today).Count().ToString()

rowCount is of DataType System.String

Please find the below workflow for better understanding:
Test (1).xaml (13.3 KB)

Regards
PS Parvathy

[Parvathy (PS Parvathy)]

Hi @anand_kumar4

Use the below updated workflow for analyze different approaches:

Test (1).xaml (17.6 KB)

Regards
PS Parvathy

[anand_kumar4 (anand kumar)]

getting error below like .. changed integer to double also but not working

[Parvathy (PS Parvathy)]

Hi @anand_kumar4

Hope your query is resolved. If yes, please mark my post as solution to close the loop.

Forum FAQ - How to mark a post as a solution

Forum FAQ - How to mark a post as a solution This document is part of our beginners guide. This article will teach you how to properly mark a post as a solution. We were closely following our last UiPath Forum feedback round! topic and carefully extracted all the bits of feedback that was provided. As such, we would like to tackle the topic of solutions on our Forum and how to properly use them. When is the topic resolved? The topic can be considered resolved when the topic author has found…

Regards
PS Parvathy

[anand_kumar4 (anand kumar)]

thanks @Parvathy @prashant1603765 given solution..