Issue with Date conversion from Text to CDate with Format

[rakotonirinas]

Hello everyone,

I would like some help please on this issue I have with Date. See workbook as an attachment.

The purpose of the userform is to capture new data onto the Data sheet. On the Userform, each time the FY is added on the ComboBox1, it automatically update the list in the ComboBox 2 to corresponding financial year month but stored as text, I believe e.g.

Please Login or Register  to view this content.

.

I have a hard time to understand why is it when it is recorded onto the Data sheet, the full given date adds the day 19 as in 19/06/2019, where it should be at least 01/06/2019 for a June 2019 date, i.e., Jun-19.

This will be important because, later on I will check whether there is a duplicate so it looks like It won't find it if the date has the day 19 as the full date.

I've tried everything but never resolved the issue. I really appreciate your help. Thank you.

result.PNG

[6StringJazzer]

In Excel Jun-19 is interpreted to mean June 19 of the current year (not June of 2019). I would use DateSerial(1,6,2019) instead of Jun-19.

[rakotonirinas]

In Excel Jun-19 is interpreted to mean June 19 of the current year (not June of 2019). I would use DateSerial(1,6,2019) instead of Jun-19.

I’m not sure I understand your statement. June is a reporting month which in effect means the June month of the year, irrespective of the day. Hence I would like it to display June-19 or any format that will show its June 2019.

[6StringJazzer]

To Excel, "Jun-19" does not mean June 2019. It means June 19th of the current year.

You should store the date as June 1, 2019 [DateSerial(1,6,2019)] and then display it in "Mmm-yy" format.

Because you want to show your month in the combo box using a non-standard format, I suggest you add a hidden column to the combo box that has the actual date. You can also calculate the date instead of listing them all. This simplifies your code quite a bit.

I have attached a revision to your code/worksheet to illustrate.

[rakotonirinas]

Hello,

Thank you for your help on this. I have a feedback and a question on this.

Feedback. I did test the macro you did but every time, it has always recorded Jan-19 in the reporting month result irrespective of whether I choose another month of the year, as per the attached.
I reckon, it may be something to do with the code

Please Login or Register  to view this content.

. I'm not very well familiar with the Index function so was unable to figure it out.

Question:

Because you want to show your month in the combo box using a non-standard format, I suggest you add a hidden column to the combo box that has the actual date.

On the ComboBox2, I think the reason in which I would like it to display this way is because it would then be easier for the user to see which month of the year is picked and I added the year as well just to make sure they are aware of how the FY works.
However, I'm happy to have it written differently if it makes things easier. Furthermore, the most important part of this exercise is the recording part, because what it meant to show is, as sales report, the product, the quantity and the revenues made with that product on that particular month. I will be happy to consider any alternative to show this reporting month differently if possible at all, as long it keeps the consistency.

Thank you.

[romperstomper]

Use a 4 digit year in your text.

[rakotonirinas]

Use a 4 digit year in your text.

Hi rorya, I'm not sure I follow up properly. Would you mind expanding, please? Thank you.

[romperstomper]

If you use "May-2019" it can't be misinterpreted as 19th May. Then simply format the cell with a "mmm-yy" numberformat if you want it to display as May-19.

[rakotonirinas]

Rorya, I managed to execute based on what you said and it seems to be working perfectly at the moment. Thanks for the tip. Much appreciated.