Excel - Get Cell - Dates - VisualCron - Forum

Community forum

Lee
  •  Lee
  • Paid support Topic Starter
2019-09-16T09:47:48Z
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.
Gary_W
2019-09-16T13:31:56Z
If you are formatting the date on the way in, note that in VC the format "mm" means minute where "MM" means month.
Lee
  •  Lee
  • Paid support Topic Starter
2019-09-16T13:54:20Z
Originally Posted by: Gary_W 

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*.
Excel Date.JPG

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.

Gary_W
2019-09-16T14:15:40Z
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".
Lee
  •  Lee
  • Paid support Topic Starter
2019-09-16T14:45:31Z
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

Lee
  •  Lee
  • Paid support Topic Starter
2019-09-16T14:50:04Z
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".
Gary_W
2019-09-16T14:55:32Z
I'm surprised this one hadn't been found sooner! This should have been caught long ago in testing.
Support
2019-09-16T14:59:24Z
Originally Posted by: 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".



Thank you for reporting this. We're investigating this now/forwarding it to the proper team.
Michael
Support
http://www.visualcron.com 

Please like  VisualCron on facebook!
Lee
  •  Lee
  • Paid support Topic Starter
2019-09-16T15:01:17Z
Originally Posted by: Gary_W 

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




Gary_W
2019-09-16T15:04:20Z
Well I don't know about that but I'm happy to assist as we do a lot of work with spreadsheets here too. 🙂
Scroll to Top