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.
| 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 6 years, 8 months 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.
