Cells in the same column showing different date format

Ramadan2512

Board Regular
Joined
Sep 7, 2024
Messages
68
Office Version
  1. 2021
Platform
  1. Windows
I have a problem to sort dates either from oldest to newest or vice versa
my worksheet with a table and tow columns "G" & "H" to add short dates, but when i try to sort dates I get a mess and dates are not sorted in the right way, when I treid to figure out the problem I found that when I select a date cell, there are some cells like "G272" showing date (in formula bar) as for example 05.11.2024 and other cell like "G274" showing the dates as 01.12.2024 as you can see in attached screenshots . while as you can see in the sheet itself both dates are showing as 24 not 2024
I Tried to copy and paste date format for all cells but still nothing changed in the formula bar

For your info if this might help - I was inserting dates manually first then I added a code to insert date automatically based on other cells value so there are some cells were filled manually and other through VBA

please let me know how to fix that

111.png
222.png
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
This is probably due to the fact that your dates are not real dates but text. Remove the manual alignment of the G and H columns. If the contents are left-aligned, they are text. There are several methods in the forum to make real dates from text. See https://trumpexcel.com/convert-text-to-date-excel/ for some of these methods
Thanks for your reply but I'm sure that all cells formatting is set as date not text
 
Upvote 0
Thanks for your reply but I'm sure that all cells formatting is set as date not text
The fact that the cells are formatted as Date does not necessarily mean the underlying values are actual dates.
Follow the post 2 suggestion about removing the (centre) alignment from columns G & H and see if all the values move left or right or some of each.
My guess is that cell G272 will become right-aligned and G274 will become left-aligned.
 
Upvote 0
The fact that the cells are formatted as Date does not necessarily mean the underlying values are actual dates.
Follow the post 2 suggestion about removing the (centre) alignment from columns G & H and see if all the values move left or right or some of each.
My guess is that cell G272 will become right-aligned and G274 will become left-aligned.
Yes you were right - it works - sorry for not understanding the point of view - I though it means cell formatting like General, text, date..etc
Thanks for your help
 
Upvote 0

Forum statistics

Threads
1,224,516
Messages
6,179,231
Members
452,898
Latest member
Capolavoro009

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