How to convert Excel date serial number to vb regular date within BarTender script
Hi there,
need to How to convert a string containing an Excel date serial number to a vb regular date within BarTender script.
Any idea over there ? Thanks a lot for your help
-
There maybe another way to do it but as Excel uses the 1st Jan 1900 as 1 then you could just do a Date Add command to add the DateSerial value to this.
I would suggest setting the VB as an Event Controlled Script set to OnNewRecord and the code I used was as follows (with the Database [art being added ny double clicking on the database field name in the Script Assistant in the column to the right.
Value = 1/1/1900
Value = DateAdd("d",Value,Field("numchckxls.Sheet1.dateserial"))
Print Preview screenshot:
0 -
This works fine. thks a lot !
0 -
Hi Peter,
still have issue within BarTender script as DateAdd raises error such as:
OnAutoSelectEvent(Line 11): Incorrect procedure argument or call
That's my script:' Excel Serial date
'dim serialAdd
'serialAdd=Field("c_acemo4.c_acemo4.DATE_LIVRAISON_GIS_21")'mySerial=CDbl(serialAdd)
' Excel date reference
refDate = 1/1/1900' add days from reference date
Value = DateAdd("d",Field("c_acemo4.c_acemo4.DATE_LIVRAISON_GIS_21"),refDate)' Month to add
Value = DateAdd("m",Field("c_acemo4.c_acemo4.NBRE_MOIS_GARANTIE_22"),Value)This is he csv data file:
id;LS;DATE_LIVRAISON_GIS_21;NBRE_MOIS_GARANTIE_22
17;2016078251;45194;36DATE_LIVRAISON_GIS_21
is the Excel serial date
NBRE_MOIS_GARANTIE_22
is the Month number to add
Could you plse help further ? Thanks if you can
have a great day0 -
So you are trying to add X number of months to a date set from the Excel serial date.
You can drastically simplify the code and do not VB for all of this process.
NB with the Data Source set to VB adjust the Data Type to Date and choose how you want the field displayed.
For the OnAutoselected set the Value to
Value = Now()
(This is just a default that will be used if the VB is not being used.)
The for the OnNewRecord all you need is
Value = 1/1/1900
Value = DateAdd("d",Value,Field("c_acemo4.c_acemo4.DATE_LIVRAISON_GIS_21"))
Now come out of the VB and click on the Transforms and Offset and set the source to Database and choose your field and change the units to months.
0 -
Hi Peter,
Tks for your message.I'm quite lost between your 1st and last email.
I 'd resume my full need to be more clear. My fault :
this is my data csv source:ID;ExcelSerialDate;ExtraMonthWarranty
1;45201;36
2;45231;18
3;45261;36
1) I need to convert ExcelSerialDate to a regular date to be display on the label
2) i need to add ExtraMonthWarranty to this regular to be displayed as well
i apologise for my previous poor explanation.
Could help me for the script again please ?
Thanks a lot
Enjoy the day :-)0 -
The simplest way for that would be to make the offset date field an Object Value field and link it to the Text field that gets and converts the excel serial value to a standard date type.
On the new field, set the Data Type to Date and choose how you want this displayed (there is a custom format if you need something different such as Julian dates) and then choose Offset from the Transforms tab, and choose Database as the source selecting your offset field and make sure to change the Units to Months.
0 -
Hi Peter, i succed !
I learned new stuff regarding BarTender.Such a great tool !
Thanks a million
0 -
Hi Peter, sorry to come again. it works fine when i test with brand new label but not when i apply to my customer label.
it is 2 days i try but the field date do not appears when i put the offset date on it.
is there a way to send you my label and the corresponding csv file in order you to check what is wrong with my label ?
Thanks a lot for your help0 -
Does the date part work without the offset?
If so I would check that the offset info is coming through correctly by adding a temp test field on with those details. Did you also tick the apply after data entry?
0 -
yes the date part work without the offset
yes the offset info is correct when adding a separate temp field
i working with 2016 R9 (3160) version both side client and server
i feel like the offset function works so soi dont really know what to do more
0 -
Been a long time since I used 2016 and so I cannot recall all the functionality but we could instead go back to controlling it via a VB string as above.
For the offset field start with the same VB as per the Data Serial to get the base date you require, ie
For the OnAutoselected set the Value to
Value = Now()
(This is just a default that will be used if the VB is not being used.)
The for the OnNewRecord all you need is
Value = 1/1/1900
Value = DateAdd("d",Value,Field("c_acemo4.c_acemo4.DATE_LIVRAISON_GIS_21"))
then add
Value1 =
and link to you month offset field to this (Field("c_acemo4.c_acemo4.NBRE_MOIS_GARANTIE_22"),Value) I think it is) and then add the following line
Value = DateAdd("m",Value1,Value)
0 -
Thanks Peter.
its seems to works fine for me, i mean the add month script to have an end Date warrenty.now based on the end date warranty result, and to complete my project, i wish to create a barcode 128 with my ID+end warranty date Month (mm) + end warranty date Year (yy), for example: with an ID=1234567890, month as september and year as 2026, my barcode should be: 12345678900926
could you please help to achieve with that as i tried but do not succed :-/
Thanks a lot for your patience and help
enjoy the day0 -
You can make the barcode up out of different substrings as shown in the image below.
For the date element and the VB, create the VB field and then on the Type tab set it to Date and adjust the way you want it displayed in my image I used Custom with a MMyyyy pattern.
0 -
Hi Pete, thanks. I notice it works fine as long there is only one record but as soon there are more than one record to print, the composed barcode displays the month and year of the next record. I dont know why. may be because there is code on NexRecord Event to calculate th e end date warranty ? i m really lost to find out why.
Could you help please ? Thanks again.0 -
The VB routines should be set to run OnNewRecord so that whenever a new database record is going to be printed the VB kicks in to calculate the warranty period.
Are the dates just used in the barcode or are they contained in a text object also?
0 -
Hi Pete, as a response see images below:
VB routines are set to run OnNewRecord as you can see.
May be i do wrong in other place ?0 -
What is this VB? As that seems to have an error and perhaps that is effecting this.
0
Please sign in to leave a comment.
Comments
17 comments