Excel Date Formatting Issue

[Sundance_Kid]

Hi
I have a few files of data I need to consolidate, I am not pulling from source but just been given the files to work with.

The date is tagged as general in terms of category in format cells.

I am encountering an issue where one of the files has the date in the format dd/mm/yyyy when you look at the date in the excel table but when I click on General in Format Cells it is giving me a number - so for 13/08/2023 it shows 45151 when I click on format cells and go to General.
All the other files when I click on general have the actual date appearing under format so 13/08/2023 would appear like that under General in the other files. The majority of the files seem to have the date appearing and not the number under general.

Any ideas how I can streamline so the minority also show the date under general and not a number?
Thanks

 

[Joe4]

It is important to understand how Excel stores Dates and Times.
Dates are really just stored as numbers in Excel, specifically the number of days since 1/0/1900.
And Time is just the fractional component of one day, i.e. 6:00 AM is 0.25 (6/24).
So dates/times are really just number with special date/time formats applied to them.

So if you enter a valid date in Excel, and then change the format of that cell to "General", you would/should expect it to show a number like "45151".
If it does not change, then you do NOT have a validly entered Date in Excel, but rather a Text entry ("date entered as text").
Note that formatting ONLY affects numeric values entered as numbers, and NOT anything entered as text.

 

[Sundance_Kid]

It is important to understand how Excel stores Dates and Times.
Dates are really just stored as numbers in Excel, specifically the number of days since 1/0/1900.
And Time is just the fractional component of one day, i.e. 6:00 AM is 0.25 (6/24).
So dates/times are really just number with special date/time formats applied to them.

So if you enter a valid date in Excel, and then change the format of that cell to "General", you would/should expect it to show a number like "45151".
If it does not change, then you do NOT have a validly entered Date in Excel, but rather a Text entry ("date entered as text").
Note that formatting ONLY affects numeric values entered as numbers, and NOT anything entered as text.

Thanks Joe.
With the above in mind, is there any quick way to convert a number to a text entry “date entered as text”?

 

[Joe4]

Thanks Joe.
With the above in mind, is there any quick way to convert a number to a text entry “date entered as text”?

That would probably require VBA.

If you select the range you want to apply it to, and then run this VBA code, it should do what you want.

Sub MyMacro()

    Dim cell As Range
    
'   Change format of selection to text
    Selection.NumberFormat = "@"
    
'   Loop through all cells in selection
    For Each cell In Selection
'       Check to see if entry is numeric (valid date)
        If Application.WorksheetFunction.IsNumber(cell) Then
'           Convert entry to text
            cell.Value = Format(cell, "dd/mm/yyyy")
        End If
    Next cell
    
End Sub

 

[Sundance_Kid]

That would probably require VBA.

If you select the range you want to apply it to, and then run this VBA code, it should do what you want.

Sub MyMacro()

    Dim cell As Range
   
'   Change format of selection to text
    Selection.NumberFormat = "@"
   
'   Loop through all cells in selection
    For Each cell In Selection
'       Check to see if entry is numeric (valid date)
        If Application.WorksheetFunction.IsNumber(cell) Then
'           Convert entry to text
            cell.Value = Format(cell, "dd/mm/yyyy")
        End If
    Next cell
   
End Sub

Thanks, I was able to change it by just going =Text(A1,”dd/mm/yyyy”)

 

[Alex Blakenburg]

Unless you are exporting the file that is a terrible idea. If they are formatted as dates you can do a lot with them if you format them as text than you really limit their usefulness.

 

[Joe4]

Thanks, I was able to change it by just going =Text(A1,”dd/mm/yyyy”)

That is fine, if you want to return the value in a different cell/column.
I was under the impression that you wanted to update it in place, not in a different cell/column.
That is what my code will do, it will update it in place without having to use extra cells or columns.