×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel Date format

thread68-1824721

Excel Date format

Excel Date format

(OP)


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

RE: Excel Date format

How about...



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

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

---- Andy

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

RE: Excel Date format

(OP)
Dhookom

I tried that and it does not change the format

RE: Excel Date format

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.

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



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

RE: Excel Date format

How about my solution... ponder

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 sad

---- Andy

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

RE: Excel Date format

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

RE: Excel Date format

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 (Ctrl-; 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

RE: Excel Date format

(OP)
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.

RE: Excel Date format

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

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

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

RE: Excel Date format

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 (first column)

CODE

Option Explicit

Sub techie131()
Dim R As Integer

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

End Sub

If you deal with those dates in Column B, change blue 1 to 2 thumbsup

---- Andy

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

RE: Excel Date format

(OP)
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.

RE: Excel Date format

"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.

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"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!

RE: Excel Date format

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):

CODE --> M_(Power_Query)

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-forma...

combo

RE: Excel Date format

(OP)
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

RE: Excel Date format

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* and resaves the excel file to wherever they want"

* 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
Option Explicit
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

RE: Excel Date format

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"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!

RE: Excel Date format

(OP)
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.

RE: Excel Date format

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"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!

RE: Excel Date format

"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):
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

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...ponder


---- Andy

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

RE: Excel Date format

@techie131, Andy just demonstrated that IMPORTING text data with dates INTO Excel is much superior to OPENing a text file with dates WITH Excel

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close