Converting an Excel Date to a date in Tulip (via BarCode)

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?

Regards Chris

Hey Chris - I am checking with the team to see if anyone has any good ideas about this one!

@ChrisF Okay I have something

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.

Hopefully that helps some!

@ChrisF Is it possible to change the original barcode content to represent the date in the tulip-native format?

(not sure that is exactly the right format…)

=TEXT(B26, “yyyy-mm-dd”)

1 Like

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()

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 :slight_smile:
Regards Chris