Excel Date Formatting Issue

Sundance_Kid

Board Regular
Joined
Sep 2, 2017
Messages
155
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Upvote 0
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”?
 
Upvote 0
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.
VBA Code:
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
 
Upvote 0
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.
VBA Code:
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”)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,375
Messages
6,171,732
Members
452,419
Latest member
mapa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top