Dates Exported To Excel Are Not In Date Format

[BjornSkifs]

Hi!

 

I am exporting a view to excel. When I try to perform the operation year(a2) on a date, then I get an error saying that the cell contains text and not a date.

 

I.e. it seems that a date i Caspio is not exported as a date to excel.

 

Regards

Bjorn

 

[jonathanr]

Hi Bjorn,

 

You are right. A date in Caspio Bridge is not exported as a date in Excel.

 

Could you give more details on what you are trying to accomplish with year(a2). 

 

Thank you,

Jonathan

[BjornSkifs]

Hi Jonathan,

 

Thank you for the confirmation.

 

This is probably a longer explanation than you asked for, but here it goes. This is what I want to do:

1. I use DataHub to export a view with sales data to Google Drive.
2. Then I want to perform a few simple calculations on this data. For example I aggregate the data per month. This is where I need year(a2), but also month(a2).
3. Then I want to use a feature in Google spreadsheet that creates a gauge to show sales during the current month. I know that there are people who argue against gauges as being an inefficient way to display data, but our sales people just love it. This gauge is then shown on our internal Caspio page as an iframe.

I have two problems with this. First, since the date is not recognized as such in excel, I can't do the aggregations. Second, it is cumbersome to make Google Drive automatically transform the excel file to a google spreadsheet. For these two reasons I have now abounded the project.

 

Instead I create an anonymous web page and scrape the data from there with Google spreadsheet. Then I can create the gauge. I don't like this solution much, so I'd prefer another solution.

 

I know that I can create a bar in Caspio and display the bar instead of the gauge, but the gauge is simply much more fun.

 

At a more general level, being able to export data to Excel or Google spreadsheets, and then to have it automatically presented in custom made charts, would be a big advantage.

 

Thanks

Bjorn

[Dal357]

In case anyone comes across this with the same issue, the following worked for me (in both Excel and Google Sheets):

=year(datevalue(a2))

It converts the text to MS date code, then takes the year.

Regards,

Dal