Problem with Dates being "read" based on different languages in computer

[0Go1DKabum]

Hi, guys.

I have built a spreadsheet to control rotation of crews but for some reason it is behaving oddly depending on the computer it is opened.

Whenever I open it in a computer with laguange set in Portuguese the flights are not returning the pax list unless I have to change the date format to PT BR (DD-MMM-YYYY), if I open in English language computer then it pops the same problem again and I have to change the dates manually too on the spreadsheet. Since I own a company computer and a personal computer I run into this mess all the time.

I would like to know if there is a way to have VBA to read the LOCALE and respect it without breaking erros. Even if it is a check on the WorkbookOpen event to read and trigger some variables for English or Portuguese.

The piece of code that is not working is this "IF" that needs to compare the user input to the range on the spreadsheet. The application.match always returns a error #N/A hence it does not do anything.

Dim x, y(), z(), yy, zz, PON(), POFF(), i As Long, ii As Integer, iCol As Long
Dim iY As Integer, iZ As Integer, iON As Integer, iOFF As Integer, sDT As String

    sDT = Format(Me.txtFlightDate, "dd-mmm-yyyy")
    'sDT = CStr(Me.txtFlightDate)

    shtParameters.Range("FlightDate").Value = sDT

    If Not IsError(Application.Match(sDT, [Dates_Operations], 0)) Then
            iCol = Application.Match(sDT, [Dates_Operations], 0)
            x = [tblOperation]
                For i = 1 To UBound(x, 1)
                    
                    If x(i, iCol) = "E" Then
                        iY = iY + 1: ReDim Preserve y(1 To 11, 1 To iY)
                            y(1, iY) = iY ' Number
                            y(2, iY) = x(i, 1) ' Team / Company
                            y(3, iY) = x(i, 2) ' Position
                            y(4, iY) = x(i, 3) ' Name
                            y(5, iY) = x(i, 4) ' SISPAT
                            y(6, iY) = x(i, 5) ' Country
                            y(7, iY) = x(i, 7) ' Quarentine
                            y(8, iY) = x(i, 8) ' PCR
                            y(9, iY) = x(i, 9) ' Trans SBCB
                            y(10, iY) = x(i, 10) ' POO
                        ...

I appreciate any help!

The link to a sanitized copy is here: Sanitized Copy

 

[GWteB]

Currently you don't use dates in your workbook, but text strings. Perhaps it would be better to switch to dates (which are actually numbers) so they will always be found, regardless of locale.

 

[cmowla]

Yes he does. Check the sheet POB and SEATS. (Cell AM4, for example.)

 

[GWteB]

Yes he does. Check the sheet POB and SEATS. (Cell AM4, for example.)

I did and I saw that, but I'm referring to his issue and within the named ranges involved NO dates are used.

 

[0Go1DKabum]

Currently you don't use dates in your workbook, but text strings. Perhaps it would be better to switch to dates (which are actually numbers) so they will always be found, regardless of locale.

That is something that is nagging me... I changed that back and forth but somehow it is always returning as string instead of date serial. As you could see on the "POB and Seats" tab they are dates, but the Weekday Formula I have there stops working if I edit any cell on that range.

What am I doing wrong here?

 

[0Go1DKabum]

Yes he does. Check the sheet POB and SEATS. (Cell AM4, for example.)

That was left untouched, but if you check the Tab "Vessel" or "Vendor" they will be different, as strings and that is where my code is intended to search for personnel.

Also I tested to return weekday formula on AM in laptop in portuguese and it return #value error.

 

[0Go1DKabum]

I have just made a test as I just realized I am using A Table (ListObject) and its behavior is diferent than a commom range....

The first row is a commom range and I can easily change its format and it is beaing treated as a number. As for the second row with 44466 it is being treated as number but won´t be changed to the formatting I need unless I change edit "F2" the cell and it will show the formarting I need "30-SEP-21" or "30-set-21".

Any ideas on how to work this around using table object?

 

[0Go1DKabum]

Just found an article in Microsoft website with some information on tables structure:
Table structure:

• All column headers are text strings But they don’t require quotes when they’re used in a structured reference. Numbers or dates, such as 2014 or 1/1/2014, are also considered text strings. You can’t use expressions with column headers. For example, the expression DeptSalesFYSummary[[2014]:[2012]] won’t work.

I will work on a hint they offered by turning off header row. Keep you posted on any advances I make.

 

[0Go1DKabum]

Hi, I managed to fix the issue...

The header can only be a string so I decided to change the user input to a string and format it the same way it was formatted on the header.

sDT = CStr(shtManifest.Range("EnterFlightDate").Value)

     shtParameters.Range("H18").Value = Format(shtParameters.Range("H18"), "DD-MMM-YYYY")
     
     shtParameters.Range("FlightDate").Value = Format(shtParameters.Range("H18"), "DD-MMM-YYYY")
     
     sDT = shtParameters.Range("H18")

Now it is working!

 

[GWteB]

Glad it's sorted and thanks for letting us know.