Compare Excel dates (dd/MM/yyyy) to Today's date (dd/MM/yyyy)

Help Studio
, ,
1

Hi,

My automation has been working great until yesterday.

I have a spreadsheet that has dates in column 1. I have formatted this in Excel so many times and ended with Text format so the format dd/MM/yyyy is preserved

The Read Range in UiPath has PreservedFormat ticked.

Assigned:
Today = Date.Now.Tostring(“dd/MM/yyyy”)

I have added a For each row
sDates = row(“Date”).ToString

I want to check if sDates = Today and then the flowchart follows…

I have tried all these but none has worked

convert.ToDateTime(sDates).ToString(“dd/MM/yyyy”)row(“Date”).ToString

CDate(row(“Date”).ToString.Trim).ToString(“dd/mm/yyyy”)

Date.ParseExact(“dd/MM/yyyy”,row(“Date”).ToString.Trim,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd/MM/yyyy”)

Datetime.ParseExact(sDate,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture)

Get Row Item > Format Value…

I’m still learning the coding world. Please let me know what I’m missing and what I should do.

TIA!

2

Hi @j8zel ,

If your Date columns has value like “19/09/2021”
then you can easily check inside the for each row in Data table

row(“Date”).tostring = Date.Now.Tostring(“dd/MM/yyyy”)

image

3

@j8zel

Can you check the format of the format of the date by using the log message inside the for each row and check

So use If condition and write as row(“Date Column”).ToString = Now.ToString(“Format of the Date”)

Hope this may help you

Thanks

4

Hi @j8zel

Is it possible to share the sample excel file with us.

Thanks.

5

Hi @j8zel

Try this appraoch,

I had an excel file, like this
image

See dates are in different formats, see how we can check the dates are today or not using the workflow

Main.xaml (13.0 KB)

Here i had used general concept to check if dates are today or not without converting to a specific format.

Regards,
Nived N

6

Hi,

If the PreservedFormat isn’t marked, the solution of get row item–>Format value is working.
I prepared sample workflow for this problem.
I hope it works for you.

CompareDates.zip (44.2 KB)

Best Regards,

7

Don’t use today as a string. Use ParseExact to convert the date string from Excel into a datetime, then compare to Now. You don’t compare dates as strings, you compare them as datetimes.

1 Like
8

Perfect. That’s exactly I have been telling/showing few others. You should not compare the dates as string.

In this case, it may work because “20/09/2021” = “20/09/2021” , but I see some are using it for checking < or > also.

2 Likes
9

Thanks Emine. I had these workings but I kept getting an error. So I gave up.

Format Value: Cannot convert Generic Value to System.DateTime

10

Thanks @postwick and @prasath17 . Could you please give me an example?

Here are my workings…

Assigned:
today = string

image
image750×198 5.11 KB

sDates = string
image

The condition in FlowDecision
image

However, the Output is this
image
sDates.xlsx (11.1 KB)

11

Hi @vignesh.ks

Here’s the excel. Cheers
sDates.xlsx (11.1 KB)

12

Thanks @manjula_rajendran and @Srini84 that’s what I currently have.

@NIVED_NAMBIAR thank you but unfortunately, that didn’t work for me.

sDates.xlsx (11.1 KB)

I have attached my screenshots on the post to postwick.

13

@j8zel - Thanks for the spreadsheet. Give me few mins I will share the screenshots.

14

@j8zel - Here you go…

image
image1249×736 44.5 KB

Since my date is 09/20 today, It correctly picked 20/09/2021 as shown in the screenshot above.

IF Activity …

datetime.ParseExact(currentrow(0).tostring,"dd/MM/yyyy",System.Globalization.CultureInfo.InvariantCulture).date = now.date

Note: I didn’t use CDate since my Locale time settings are MM/dd/yyyy. If your Locale settings are dd/MM/yyyy then you can use CDate.

I also did not use Preserve format.

Hope this helps…

1 Like
15

Yes,I saw same error, this problem because of preserved format. Can you try again without preserved format.

image
image705×234 10.9 KB

16

Hi @j8zel ,

Here I attached the workflow using your excel. See if it helps.

CompareDate.xaml (8.2 KB)

17

Hi @j8zel

Please find the below xaml file for your reference.

Compate_Date.xaml (10.5 KB)

Try this and let me know whether it meet’s your expectation.

Feel free to reach us at any time if you have doubts.

Thanks. Happy Automation

18

Do not convert Now to a string. Leave it as a datetime. Now is already a datetime.

Use ParseExact to convert the string from Excel into a datetime and then compare directly with Now.

19

Thanks everyone. @prasath17 example worked for me and it is stable. Cheers.

1 Like
20

Thank you for the solution. It worked on my project flow.

1 Like