This thread is resolved. Here is a description of the problem and solution.
Problem:
I am trying to get my dates and times in a cell in a spreadsheet to display properly in the custom fields i have added through toolset types to the date/time field i am importing them into.
From my research it appears that the data is stored in the db as a unix timestamp.
If this is the case, if there is any help you can provide in getting my cells to be formatted properly, it would be really appreciated.
Solution:
you need to setup custom formula for it, I have tried it in Google spreadsheets, the formula works fine:
=(A1-DATE(1970,1,1))*86400
See screenshot:
https://cdn.toolset.com/wp-content/uploads/2018/03/629934-formula.JPG
Relevant Documentation:
https://www.extendoffice.com/documents/excel/2473-excel-timestamp-to-date.html#a2
This is the technical support forum for Toolset - a suite of plugins for developing WordPress sites without writing PHP.
Everyone can read this forum, but only Toolset clients can post in it. Toolset support works 6 days per week, 19 hours per day.
No supporters are available to work today on Toolset forum. Feel free to create tickets and we will handle it as soon as we are online. Thank you for your understanding.
| Sun | Mon | Tue | Wed | Thu | Fri | Sat |
|---|---|---|---|---|---|---|
| - | 9:00 – 13:00 | 9:00 – 13:00 | 9:00 – 13:00 | 9:00 – 13:00 | 9:00 – 13:00 | - |
| - | 14:00 – 18:00 | 14:00 – 18:00 | 14:00 – 18:00 | 14:00 – 18:00 | 14:00 – 18:00 | - |
Supporter timezone: Asia/Hong_Kong (GMT+08:00)
This topic contains 3 replies, has 2 voices.
Last updated by Luo Yang 6 years ago.
Assisted by: Luo Yang.
Tell us what you are trying to do?
I am trying to get my dates and times in a cell in a spreadsheet to display properly in the custom fields i have added through toolset types to the date/time field i am importing them into.
From my research it appears that the data is stored in the db as a unix timestamp.
If this is the case, if there is any help you can provide in getting my cells to be formatted properly, it would be really appreciated.
I can an example of a formula to use to convert my cells here but it isn't working for me:
hidden link
regarding screenshot:
cl_date is the original date data, and cl_date0 is my attempt to remove the last 2 zeros to help prepare the cells for transformation into unix timestamps.
Hello,
There isn't such a built-in feature within Toolsets, and you are right, you need to setup custom formula for it, I have tried it in Google spreadsheets, the formula works fine:
=(A1-DATE(1970,1,1))*86400
See screenshot formula.JPG
You can put your CSV file into google drive disk, setup the formula and test again.
