VBA issue with dates - format is inconsistant and at times wrong

Hi,

I received some assistance with a macro and I can't seem to fix the date issue. The macro consolidates data from Sheet1 and puts it into a structured format on the tab "Consolidated".

If you look at the Date column on the "Consolidated" tab it will become clear as to what the issue is.

I've also noticed that there are cases where an original date in Sheet1 is 08/01/16 i.e. 8 Jan 2016 (Sheet1 row 3364) and this has now been converted to 2016-08-01 i.e 1 August 2016 (Sheet1 row 3364).

I've attached the workbook with the VBA code. Any help would be greatly appreciated.

Originally Posted by concatch

Hi,

I received some assistance with a macro and I can't seem to fix the date issue. The macro consolidates data from Sheet1 and puts it into a structured format on the tab "Consolidated".

If you look at the Date column on the "Consolidated" tab it will become clear as to what the issue is.

I've also noticed that there are cases where an original date in Sheet1 is 08/01/16 i.e. 8 Jan 2016 (Sheet1 row 3364) and this has now been converted to 2016-08-01 i.e 1 August 2016 (Sheet1 row 3364).

I've attached the workbook with the VBA code. Any help would be greatly appreciated.

Just to confirm I'm seeing this right. The dates are being read the wrong way around. For example the first date in
the 'Consolidated(2)' worksheet should be 7/12/2015 but in fact reads 15/12/2007?

Here's what I see the problem as being:

I believe you are in South Africa. I'm also of the understanding that in South Africa your default date format is
YYYY/mm/dd . If I'm right on that then that is the way your system is set up to read dates.

When I select a date cell in Sheet1.ColumnB (cell B10) it is set to format 'General'. So when your system tries to interpret this as a date, all it sees is some binary value, maybe a number, whatever, but it tries to interpret is as a date when it is converted to a date and placed in your 'Consolidated(2)' spreadsheet.

Solution: I believe this should work. Select Column B in Sheet1 and Right Click > Format Cells > (under the 'Category' column select 'Date' in the box and to the right of that select the number format you are transferring into your Consolidated(2) spreadsheet date column). I believe this is using YY/MM/DDDD. So there are several examples in the 'Format Cells' wizard you can choose from. Select a date reflecting 'YY/MM/DDDD'. In my box it gives me an example date of '14/03/2001' so I just select that.

Then...... Rerun your macro and the dates should be read properly and thus.. transfer properly to your 'Consolidated(2) spreadsheet. If this doesn't fix the problem you may need to enforce the source date format by building a step into your macro.

I hope this helps.