Date mismatch in rows

[msn1712]

Hello,

I need a macro to make the date format standard across all the rows.

For eg. There are around 50,000 rows (which will change on daily basis) which has the dates. The problem is that the data I am using is for global level and so the date/time which I get is not in a standard format. So, if I have a data which is for Aug 1st, it's reading as Jan 8th, etc. Also, there are some dates which are actually in text format in the same row.

I tried creating some formula's (like splitting the day, month, year and concatenate), but it's not working.

Now what I am trying to do is, no matter the data is in any date format, I need to get the standard date format for each rows.

Hope someone can help me with this.

Regards,
Mohan

 

[Joe4]

Is the data already in Excel, or are you importing it into Excel?
The issue is the some places put month before day and others put day before month. So 1/8 could mean January 8 or August 1.
If the data is already in Excel and it is already a date, changing the date format will not change the actual underlying date, that is already set in stone.
If you were to change it, that change would be applied to ALL the dates. Otherwise, how could you tell which ones should be changed and which ones shouldn't?

So, we would need the answers to the following questions:
- Where is the data coming from (is it already in Excel or in some sort of Text file)?
- What are all the different date structures in the file?
- Which structures need to be changed and how?

Providing some actual examples may be helpful.

 

[msn1712]

Thank you Joe4 for your inputs..

1. We download the data from a particular tool in Excel format.
2. Below is the sample data from the report.

[TABLE="width: 258"]
<tbody>[TR]
[TD="align: right"]4/8/2017 16:10[/TD]
[TD][/TD]
[TD]Reading as Apr 8th[/TD]
[/TR]
[TR]
[TD="align: right"]4/8/2017 14:50[/TD]
[TD][/TD]
[TD]Reading as Apr 8th[/TD]
[/TR]
[TR]
[TD="align: right"]4/8/2017 22:53[/TD]
[TD][/TD]
[TD]Reading as Apr 8th[/TD]
[/TR]
[TR]
[TD="align: right"]1/8/2017 10:35[/TD]
[TD][/TD]
[TD]Reading as Jan 8th[/TD]
[/TR]
[TR]
[TD="align: right"]1/8/2017 10:35[/TD]
[TD][/TD]
[TD]Reading as Jan 8th[/TD]
[/TR]
[TR]
[TD="align: right"]1/8/2017 10:35[/TD]
[TD][/TD]
[TD]Reading as Jan 8th[/TD]
[/TR]
[TR]
[TD="align: right"]2/8/2017 9:10[/TD]
[TD][/TD]
[TD]Reading as Feb 8th[/TD]
[/TR]
[TR]
[TD="align: right"]2/8/2017 9:10[/TD]
[TD][/TD]
[TD]Reading as Feb 8th[/TD]
[/TR]
[TR]
[TD="align: right"]2/8/2017 9:10[/TD]
[TD][/TD]
[TD]Reading as Feb 8th[/TD]
[/TR]
[TR]
[TD="colspan: 2"]27/07/2017 07:44[/TD]
[TD]Reading as text[/TD]
[/TR]
[TR]
[TD="colspan: 2"]29/07/2017 07:39[/TD]
[TD]Reading as text[/TD]
[/TR]
[TR]
[TD="colspan: 2"]29/07/2017 07:39[/TD]
[TD]Reading as text[/TD]
[/TR]
</tbody><colgroup><col span="2"><col></colgroup>[/TABLE]



Regards,
Mohan

 

[Joe4]

Sounds like a difference in Regional Settings. One is using dates in m/d/y format, and the other is using d/m/y format.
So I would recommend changing the settings in one or the other (Excel or the program initiating the file).
Or, if you have the ability to export the file to a Text file instead of Excel, you can then use Excel to import it where you can specify the date format of the data as you are importing it.

Otherwise, you would need to write two sets of conversions formulas:
- One to handle the date entries that need to have month and day flipped
- One to handle the text entries that need to have month and day flipped and convert the entry to a date