Hi,
Trying to retrieve a Date from an Excel file using the Excel - GetCell task.
If I try to retrieve a perfectly legitimate date from a given cell I get back:
Date in Excel Cell: 13/09/2019
VC task returns: 13/00/2019

The cell in excel is a date cell using standard dd/mm/yyyy format.
Is there a workaround and or fix?


I thought maybe due to us being on v8.1.1 of VC but have also tried on 8.5.5 Pro.
I did try to search but could find no results? Has this never been raised as a problem?

EDIT: FYI, If I switch the excel file date format to mm/dd/yyyy I get result: 00/13/2019 so looks to be a problem reading month specifically.

If you are formatting the date on the way in, note that in VC the format "mm" means minute where "MM" means month.

If you are formatting the date on the way in, note that in VC the format "mm" means minute where "MM" means month.

Thanks for reply, agreed on the mm vs MM format but, in this case, the standard Excel format is being used/sent i.e. Date*.


I did try to see if there was any difference between using dd/mm/yyyy and dd/MM/yyyy and the result is the same.

The plot thickens! I get the same results on version 8.3.5 with Excel 2013. That is, if I use a direct cell reference of A2 that contains a date "4/1/2018". If I select the cell by position of row 2 column 1, I get back "43191".

if I select by cell reference range and put A2 in both, I get back "43191".

Thanks, yes I see that too.

I then, as a workaround, use an SQL task to convert the 43191 number to a date format I need:

select REPLACE(CONVERT(VARCHAR,CAST(CAST(({TASK(PrevTask,StdOut)} -2) AS DATETIME) AS DATE), 113),' ','-')

Result: '13-Sep-2019'

Messy, but let's me move on in my development.....

Many thanks
Lee

I think this is now solved, in that VC is actually looking for mm (minute) as opposed the MM (Month) in the extract process from Excel.
e.g. If I set the cell as 13/09/2019 09:56, the result (from a direct get cell B5) shows as 13/56/2019!
Looks like VC needs to be adjusted to 'get' the cell as "MM" not "mm".

I'm surprised this one hadn't been found sooner! This should have been caught long ago in testing.

I think this is now solved, in that VC is actually looking for mm (minute) as opposed the MM (Month) in the extract process from Excel.
e.g. If I set the cell as 13/09/2019 09:56, the result (from a direct get cell B5) shows as 13/56/2019!
Looks like VC needs to be adjusted to 'get' the cell as "MM" not "mm".

Thank you for reporting this. We're investigating this now/forwarding it to the proper team.

---

I'm surprised this one hadn't been found sooner! This should have been caught long ago in testing.

Agreed! At first I felt that I was doing something wrong and so did not want to raise with support and just lived with the number conversion work around, but now I have multiple client requests to consume their large Excel files hence it came back to light.
Thanks for all your help Gary, it seems I am not completely crazy after all!! 😲
Regards
Lee

Well I don't know about that but I'm happy to assist as we do a lot of work with spreadsheets here too. 🙂

Enter =DATEVALUE(
Click the cell that contains the message arranged date that you need to change over.
Enter )
Press ENTER, and the DATEVALUE capability returns the chronic number of the date that is addressed by the text date. This is the means by which Extracting date from a text string  can work.