Dates in excel is within last 3 months?

[Aaronlim]

hi all

im trying to make a process to read a column of dates, and then check if the date is within the last 3 months. how would i be able to do this?

thanks

[ranjith_udayakumar]

Hi @Aaronlim,

Kindly follow the below steps.

1. Read the excel and store it in DT
2. Use If Condition to check whether that data is falling under last month or not as below.

DateTime.Parse(row(‘date’).ToString) > DateTime.Now.AddMonths(-3)

3. It gives True if the date falls within the last 3 months and otherwise False.

Happy Automation, If you find it useful mark it as a solution.

Warm Regards,
Ranjith Udayakumar

[Aaronlim]

hi,

when i tried this, i got an error saying that the string was not recognised as a valid datetime.

the strings used are in column E and F in the pic below

[ranjith_udayakumar]

@ranjith_udayakumar

DateTime.Parse(row(‘date’).ToString) > DateTime.Now.AddMonths(-3)

Hi @Aaronlim,

Please mention the date format explicitly as like below. It will work.

DateTime.ParseExact(row(‘date’).ToString, “dd/MM/yyyy”,Nothing) > DateTime.Now.AddMonths(-3)

Warm Regards,
Ranjith Udayakumar

[Aaronlim]

hi,

it works for most cells, but for those with 00:00:00 at the side, it throws an error and is not converted. is there a way around this?

the 00:00:00 was added to some of the cells after using read and write range from a csv file.

[ranjith_udayakumar]

Hi @Aaronlim,

You can create a list of formats and then use it conversion like below.

1. Create a expected date formats as a list
   formats= {"dd/MM/yyyy","dd/MM/yyyy hh:mm:ss"}
2. Then use it like below

DateTime.ParseExact(row(‘date’).ToString,formats,new CultureInfo(“en-US”),DateTimeStyles.None) > DateTime.Now.AddMonths(-3)

Now it will pass.

Note: You should import the System.Globalization in the import panel.

[Aaronlim]

works now, thanks so much for the help!