Jump to content
  • 0
Sign in to follow this  
BjornSkifs

Dates Exported To Excel Are Not In Date Format

Question

3 answers to this question

Recommended Posts

  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...