Excel Date format

[techie131]

We download a file from a company site and the Column B always has date format as MM/dd/yy than the time. I have my region settings to set the date to yyyy/MM/dd.

I can't seem to override the setting so it converts the date in Column B to yyyy/MM/dd than the time automatically. I have tried a custom setting of yyyy/mm/dd h:mm but still not budging. Any advise would be appreciated.

I attached the data from the Column B

 

[Andrzejek]

How about...

If you want the AM/PM times:
[tt]=TEXT(A2, "yyyy/MM/dd hh:mm:ss [blue]AM/PM[/blue]")[/tt]

If you don't care about time portion:
[tt]=TEXT(A2, "yyyy/MM/dd")[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

I would use the solution at 1. How to convert text into dates in Excel using "Text to Columns".

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[techie131]

Dhookom

I tried that and it does not change the format

 

[dhookom]

These are the value I got when performing the steps. I'm not sure why the single row shows the time other than if you had set the format of the cells. You can then set your custom format.

[pre]Record Date
2023/07/24
2023/07/24
7/24/23 23:00
2023/07/24
2023/07/24
2023/07/25
2023/07/25
2023/07/25
2023/07/25
2023/07/25
2023/07/25
2023/07/25
2023/07/25
2023/07/25[/pre]


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

How about my solution...

If your 'file from a company site and the Column B always has date" as DATE (and not just TEXT that looks like DATE), you should be able to just change its format to whatever you want.

In your sample data in the attached file, all data starts with ' (an apostrophe) which makes it all TEXT and that's why changing format doesn't do anything :-(

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

Andy,
I had looked for the apostrophe but don't see it in the file I downloaded although the behavior suggests the apostrophe.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Duane,
I still have access to an older Excel 365 ProPlus (on Windows 7 machine) that shows text in the cells with the apostrophe by default. The newer versions of Excel are 'smart enough' to hide the apostrophe, which is good/bad, depending on your view.

But there is a simple test that techie131 can preform:
Let's say the data (that looks like DATEs) is in column A
In cell B2 add today's date ([tt]Ctrl-;[/tt] will do it)
Select cells A2 and B2 and change their format to General.
Today's date in B2 will display 45134, A2 will still display the text that looks like a date.

You can format cell B2 to any date format you wish, because it is a DATE.
A2 is just TEXT

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[techie131]

Andrzejek

you are correct following those instructions does give B2 45134.

Now is there a way to remove the apostrophe or tell excel disregard hidden apostrophe so that column A is interpreted as Date instead of Text.

We download this excel file so we can't change the way the data is created on the other side.

​

 

[Andrzejek]

See my first post.
[ul]
[li]Insert a column next to your 'date' column[/li]
[li]Insert the formula from my post to convert your text to dates in the new column.[/li]
[li]Select entire new column with new, real DATEs[/li]
[li]Grab its right edge with the right-mouse button when your cursor looks like this[/li]

[li]move it right and back left[/li]
[li]Release the right mouse button - you will have a menu next to your mouse pointer[/li]
[li]Select: 'Copy Here as Values Only'[/li]
[li]Delete your 'original' column with dates as text[/li]
[li]You are done. [/li]
[/ul]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

I see you are a member of VBA Visual Basic for Applications, so here is a VBA solution, assuming your 'dates' are in Column A ([blue]first column[/blue])

Option Explicit

Sub techie131()
Dim R As Integer

R = 2
Do While Not IsEmpty(Cells(R, [blue]1[/blue]).Value)
    Cells(R, [blue]1[/blue]).Value = CDate(Cells(R, [blue]1[/blue]).Value)
    R = R + 1
Loop

End Sub

If you deal with those dates in Column B, change blue [blue]1[/blue] to 2

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[techie131]

Andrzejek

I did the steps you suggested. Use the formula on a new column and than did a copy values only but it still doesn't convert the date to YYYY/MM/DD even if I change it from General to Date.

Not sure what else it could be. I would prefer not to do VBA for this one as I have to show the users how to do this.

 

[SkipVought]

but it still doesn't convert the date to YYYY/MM/DD even if I change it from General to Date."

YYYY/MM/DD Is one of MANY Number Formats that a Date can have.

Option Explicit

Sub techie131()
Dim R As Integer

R = 2
Do While Not IsEmpty(Cells(R, 1).Value)
    With Cells(R, 1)
       .Value = CDate(Cells(R, 1).Value)
       .NumberFormat = "YYYY/MM/DD"
    End With
    R = R + 1
Loop

End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[combo]

If you use Power Query queries in Excel, it may be a tool for converting text to date. I converted the source to table and named it 'tDates'. Next created query from table. As my regional settings are not US, I converted the column with 'Using Locale...' in Power Query editor.
The query (text in advanced editor):

let
    Source = Excel.CurrentWorkbook(){[Name="tDates"]}[Content],
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Record Date", type datetime}}, "en-US")
in
    #"Changed Type with Locale"

Data returned to workbook, with my local settings, no other formulas or code:

Sume useful info about prosessing dates in Power Query:

https://exceloffthegrid.com/power-query-date-formats/

combo

 

[techie131]

Ok we have a workable solution. A macro was created with the following code.

It removes the apostrophe at the beginning and the date shows properly after that. So the user just opens the macro excel file and than imports the data and runs the macro on the affected columns and resaves the excel file to wherever they want.

Sub RemoveApostrophe()

For Each CurrentCell In Selection
If CurrentCell.HasFormula = False Then
CurrentCell.Formula = CurrentCell.Value
End If
Next

End Sub

 

[Andrzejek]

Great! But...
"the user just opens the macro excel file and than imports the data", selects the range of dates "and runs the macro on the affected columns[red]*[/red] and resaves the excel file to wherever they want"

[red]*[/red] if your user selects entire column, it will take for ever to run your code thru over a million rows in Excel. If he selects multiple columns, that will take even longer.

Plus, your code will error if/when your user would have
[tt]Option Explicit[/tt]
at the top of the code. (in VBA, Tools - Options... Editor tab - Require Variable Declaration check box)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Do you realize that all Excel dates that are to be used in calculations are just pure numbers?

The Date Serial Value for today, July 30, 2023 is 45137.

Regardless of how you want to display your date, the underlying value is just a number.

SELECT the entire column and Excel allows you to change the number format to YYYY/MM/DD. Should take 10 or 15 seconds.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[techie131]

SkipVought

That is what I thought too but even changing the format to YYYY/MM/DD did not resolve it. As it turns out there is a hidden apostrophe at the beginning of that data. So that is why I am opting for a macro to strip it out.

Andrzejek
Thankfully this only occurs on one company's csv file we download from. There only 100 or so rows each time so it only takes about a minute to process.

 

[SkipVought]

The apostrophe is/was there to make the cell value TEXT rather than NUMBER.

Yes, it must be remover to have a REAL usable date.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

one company's csv file we download from"

So, I created this CSV file in Notepad, which is just a text file (Comma Separate Values):[tt]
Record Date,ABCD
7/24/23 11:00 PM,adc
7/24/23 11:00 PM,adc
7/24/23 11:00 PM,adc
7/24/23 11:00 PM,adc
7/24/23 11:40 PM,adc
7/25/23 12:02 AM,adc
7/25/23 12:02 AM,adc[/tt]

And in Excel I did: Data - From Text/CSV, and I've got:

And I can format Column A (Record Date) to any data format I wish.

How do you 'move'/retrieve the data from CSV file to your Excel...


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson