Hi all,
I’m having the following task to solve:
On a Excel sheet a given date is converted to a barcode, but the date format is converted first to the days since 1.1.1900 00:00, then displayed as a Barcode. (I think that’s the way Excel handle dates).
For example: the date 16.10.2023 is shown as 45215 with the used barcode font (free3of9.ttf).
When I scan this barcode in Tulip I’ll get the days, but have to convert it back to the date.
I tried a lot of expression functions and found a combination of Add_Time() and datetimetotext() function:
The variable “s_PA” gets the scanned value 45215 datetimetotext(add_time(texttodatetime('01-01-1900 01:0am', 'DD.MM.YYYY', 'Europe/Berlin'), texttointeger(@Variable.s_PA ) - 2, 'days', 'Europe/Berlin'), 'DD.MM.YYYY', 'Europe/Berlin')
But there must be an offset, because I have to decrease the add_time value by 2 to get the correct date 16.10.2023. Without that I get 18.10.2023.
But where does the offset come from? Or where is my mistakes in thinking?
So, excel gives 1.1.1900 00:00 the serial # 1 - so the number of days you want to use is actually 45214 for the date 16.10.2023. That account for 1 of your offset days. (see a little more explanation on this link Add or subtract dates - Excel)
The other offset day, I will chalk up to timezone weirdness. Basically, I tired your expression with one timezone Europe/Berlin and the other timezone America/New_York and then I got the right day without any offset.
Hi Beth, hi @jmlowden
thanks for your answers und suggestions.
The offset 1 makes sense, but I’m interested in where the 2nd offset comes from.
Because I can’t use an other date format than the local (german) format, I solved this problem by using the excel function TEXT() ="*"&TEXT($B7;"TT.MM.JJJJ")&"*"
the pre- and postfix “*” is necessary for the correct converting in Excel to the barcode format (I read)
So, the problem is solved for the moment
Regards Chris
