Subtracting dates from web scraped data

Help
1

Hi All,

I have used data scraping to extract a column of data. from the column I extracted a cell. This cell is “6/27/2019 Thu” but I have made it a variable. Sometimes, obviously it could be “xx/x/xxxx day” depending on which month/day of the year it is (but would still be a variable).

I would like to subtract this date from todays current date. I believe to do this I need to somehow trim the day part off and then maybe convert it from data table to date? I have had no luck so far. Please let me know if you can help. Thank you!

2

Check this:

3

Thank you - I actually did read that earlier and tried converting but it is not working for me. Does anyone have a more structured response that I may be able to follow? Thank you!

4

@Double-D If the format is always going to be the same (or consistently a couple different formats), then a great way to convert from string to datetime is to use DateTime.ParseExact method DateTime.ParseExact Method (System) | Microsoft Learn - you can see the list of possible formats for datetime strings here: Custom date and time format strings | Microsoft Learn

I’ll assume your date is saved as a string variable called YourDateAsString. Create a DateTime variable (I’ll call it YourDate). In an assign activity, give it the following YourDate = DateTime.ParseExact(YourDateAsString,"M/dd/yyyy ddd",System.Globalization.CultureInfo.InvariantCulture)

If there is more than one type of format possible, you can add an array of strings instead of the single string, so it’d be YourDatesInStringArray, or {YourDateAsString,YourOtherDateAsString}

5

@Dave thanks! I just tried it again and am getting an error “string was not recognized as a valid DateTime” - I wonder if it has anything to do with the way im getting the data?

6

If you are getting it from a datatable then you’ll have to do row.item("ColumnWithDateAsString").ToString. If you don’t include the .ToString it won’t work.

The ParseExact is expecting a string format as the first input in the (parenthesis), so if it’s object or general or any other variable type it won’t work.

7

@Dave yes I am extracting my cell with:

mysinglecell = ExtractDataTable.Rows(1).Item(0).ToString

then I am separating it because it currently is (7/5/2019 Thurs) so I did:
yourdateasstring = mySingleCell.Split(" "c)(0).ToString which gives me 7/5/2019

then I am trying to convert it into a date with

yourDate = DateTime.ParseExact(YourDateAsString,“M/dd/yyyy ddd”,System.Globalization.CultureInfo.InvariantCulture)

but it is not working (I also tried changing the date format above to M/D/YYYY

I’m not sure what I am doing wrong

8

The format I gave is incorrect, switch it to "M/d/yyyy" - note the capitalization.

9

@Dave It worked! thank you, you’ve helped me twice now.

So to clarify since there will be 3 different format possibilities (x/x/xxxx), (xx,x,xxxx), and (xx/xx/xxxx), I make the formula:

DateTime.ParseExact({YourDateAsString,“M/d/yyyy”, YourDateAsString, “mm/dd/yyyy”),System.Globalization.CultureInfo.InvariantCulture)

the variable for the date will be the same regardless of the format

10

I suppose I will also have to do the same for todays date as today date can be in multiple formats as well and I need to subtract them.

11

The string array is only for the date format portion. So it’d be DateTime.ParseExact(YourDateAsString,{“M/d/yyyy","dd,M,yyyy","MM/dd/yyyy"},System.Globalization.CultureInfo.InvariantCulture) This assumes February 5th 2019 is in formats “2/5/2019”, “25,2,2019”, or “02/05/2019” formats.

To subtract the number of days between today and the datetime, you could use a timespan variable. Put this in an assign activity, where DaysBetweenDates is an integer variable: DaysBetweenDates = (new TimeSpan(Today.Subtract(YourDate))).Days

12

@Dave

The subtraction worked perfectly.

For the array, I want it to be the 3 different formats because it will depend on what day of the month (I dont want the day to ever be first) So I will put the formats as “M/d/yyyy” , “MM, dd, yyyy”, and “M/dd/yyyy”

This should work correct? I will add it for both todays date and the scraped date.

EDIT: When I try the array as:

DateTime.ParseExact(YourDateAsString,{“M/d/yyyy”,“M/dd/yyyy”,“MM/dd/yyyy”,“MM/d/yyyy”},System.Globalization.CultureInfo.InvariantCulture)

i get the error "Value of type ‘1-dimensional array of String’ cannot be converted to ‘String’.

13

I’ve never actually done it with a string array, but the Microsoft documentation shows it should work. Maybe try making a string array variable with the formats and passing the variable in? Here’s the datetime.parsexact documentation

https://docs.microsoft.com/en-us/dotnet/api/system.datetime.parseexact