I'm trying to export data from a Honeycode table to an external system as a CSV. I've got a Python script that is working pretty well but I can't get the dates to work properly. I am trying to use the raw value and add it to 01/01/1900, which the documentation says is the epoch for Honeycode. But the result comes out with a date that is 2 days after what is actually stored in Honeycode. Can anyone suggest what I might be doing wrong?
Honeycode time is in UTC, so you will need to convert date or time data from UTC to the desired time zone. Here's an article around this to help you with the calculations: UTC & Time Zone Conversion
Hope this helps, and let us know if we can help any further! 
I don't think that is the issue. I'm not reading the date from within Honeycode but from the API and using the raw value of the cell as returned by the API. As far as that goes, I would assume that raw value would be in UTC.
I checked with the team, and the Honeycode API would not change the date data when sending from Honeycode (it sends the raw data stored in Honeycode).
As that be the case, the discrepancy of that particular date data may be in the Honeycode tables. You'll want to check that the value is returning as expected in the tables and that the cell or column is formatted to Date.
Also, one way you can inspect the data would be to open the table in Honeycode where the data is stored and change the cell format from date to number (the value that is shown is the raw data that is transmitted through the API).
Let us know if that helps. 
I tried out changing the date to a number and manually performing the date calculation and it still came up off by 2 days. Below is a document with screenshots of what I did.
I believe the Honeycode date functions emulate Microsoft Excel date functions, which have some specific date-related inconsistencies - see this page (relevant points copied below for convenience): Convert Seconds & Days Since Year 0-1970.
- In Excel January 1 is serial number 1. In this converter midnight January 1, 1900 is 0, after 1 day it is midnight on January 2. To correct this you have to add/subtract 1.
- Excel incorrectly sees 1900 as a leap year (for compatibility with Lotus 1-2-3) so you have to add/subtract 1 to all days when using in Excel. Also see: Excel incorrectly assumes that the year 1900 is a leap year
Therefore when calculating the number of days since Jan 1 1900 in Excel/Honeycode, the value will be 2 days off from the actual number of days.
When I handle values between Honeycode and (non-Excel) external systems, I subtract 25569 from the Honeycode raw value to get the number of days since Jan 1 1970 (the UNIX Epoch) and perform calculations from there. (Note the real number of days between 1900 and 1970 is 25567, so we're accounting for the Excel/Honeycode offset as well)
3 Likes
Thanks @Shavina for your reply! @Michael_W_SCR - this is correct. You'll need to subtract two in your code. The serial numbers used for dates in Honeycode are compatible with how other spreadsheet programs store dates.
1 Like
Thanks for the response, @RajeshGoli. I'm not enthusiastic about the idea of subtracting one or two days from the date to get the right one. I have worked around it by processing the formatted date instead of using the raw date. It requires a little bit of extra code to allow for different date formats, but it is working for my application.
2 Likes
We use the Python xlrd library to convert Excel dates to datetime objects. For example, the Excel date 44330.5416666667 is 1pm on the 14th of May 2021.
import xlrd
from datetime import datetime
datetime(*xlrd.xldate_as_tuple(44330.5416666667, 0))
Which results in:
datetime.datetime(2021, 5, 14, 13, 0)
2 Likes
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.