Displaying dates correctly in a userform listbox.

Herakles

Board Regular
Joined
Jul 5, 2020
Messages
58
Office Version
  1. 365
Platform
  1. Windows
Hi All

I have a userform with a listbox, one of the columns representing a date in dd/mm/yyyy format or at least that is the plan.

I have a date column in dd/mm/yyyy display format.

I need to display this in the listbox.

To do this I have an additional column and use the TEXT function to represent the date as text.

The original date column displays in the listbox as mm//dd/yyyy (incorrect) and the one using the TEXT function correctly in dd/mm/yyyy (correct) as I have specified.

BUT when I substitute the value in the column using the TEXT function with the value returned by the TEXT function, it turns the date into text but using the
mm/dd/yyyy format.

Does anybody know how I can get around this problem?

Thanks for any help.












I populate an array with values from a range and then populate the listbox using the List property.

To get around the date formatting issue, I use the TEXT function in the next column in the worksheet to turn the date into text. When I do this manually
the date is displayed correctly, dd/mm/yyyy, but when I automate this, apply the formula and then change the value to that returned by the formula, the date is formatted
to mm/dd/yyyy.

What is the most robust way around this problem?
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
BUT when I substitute the value in the column using the TEXT function with the value returned by the TEXT function, it turns the date into text but using the
mm/dd/yyyy format.
How do you do that exactly? Typically, you will need CDate() to convert the text date to a true date value using your regional settings when you assign it to a cell.
 

Herakles

Board Regular
Joined
Jul 5, 2020
Messages
58
Office Version
  1. 365
Platform
  1. Windows
How do you do that exactly? Typically, you will need CDate() to convert the text date to a true date value using your regional settings when you assign it to a cell.

Thanks RoryA

Here is the code that I use.

VBA Code:
   Q = Chr(34)
    Set rngData = fncGetColumnDataRange(WSTempAssign, "OriginalDate")
    rngData.Offset(0, 1).Formula = "=TEXT($G2," & Q & "dd/mm/yyyy" & Q & ")"
    rngData.Offset(0, 1).Value = rngData.Offset(0, 1).Value

The function call fncGetColumnDataRange returns the range containing the data based upon the column heading.

Column 'G' headed 'OriginalDate' contains the original date.

The TEXT formula goes in column 'H'.

Does this help.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Probably the simplest way is to format the cells as Text after the formula has calculated and before you replace the values:

VBA Code:
    With rngdata.Offset(0, 1)
        ' apply general format so the formula will work
        .NumberFormat = "General"
        .Formula = "=TEXT($G2," & Q & "dd/mm/yyyy" & Q & ")"
        ' apply text format
        .NumberFormat = "@"
        .Value = .Value
    End With
 

Herakles

Board Regular
Joined
Jul 5, 2020
Messages
58
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Probably the simplest way is to format the cells as Text after the formula has calculated and before you replace the values:

VBA Code:
    With rngdata.Offset(0, 1)
        ' apply general format so the formula will work
        .NumberFormat = "General"
        .Formula = "=TEXT($G2," & Q & "dd/mm/yyyy" & Q & ")"
        ' apply text format
        .NumberFormat = "@"
        .Value = .Value
    End With
Thanks RoryA.

I'll give this a go soon and let you know how I get on.

Problems like this take up a disproportionate amount of time.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If all you are using this additional column for is to populate a listbox, it really shouldn't be necessary at all.
 

Herakles

Board Regular
Joined
Jul 5, 2020
Messages
58
Office Version
  1. 365
Platform
  1. Windows
If all you are using this additional column for is to populate a listbox, it really shouldn't be necessary at all.
I've adopted another approach as your suggestion did not work for my application.

Instead of feeding the listbox from an array I loop through the range row by row and for each row of the listbox column by column.
I use the AddItem method which treats all values as strings

Feeding the listbox from an array treats dates as dates but does not take the formatting from the cell format.
It also assumes that with dates where both the days and month are less than 12 are in the format mm/dd/yyyy and not dd/mm/yyyy.

Unfortunately AddItem only works for up to 10 columns but there are ways around that problem unless one wants all 10 columns visible.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
A listbox doesn't assume anything about its contents. (Your code might though. ;)) It's all just text.

The method you describe is about the slowest possible option you could use. An array is always the way to go, unless you need column headers, in which case you need a range. Using AddItem should only ever be a last resort.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,402
Members
416,026
Latest member
melvic69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top