Inside, Excel stores dates as numbers. It does not use a date type. Number is the number of days that have passed since that time (1900 or 1904, depending on the operating system, with some errors built into leap years). Time is represented as a fractional part of a number.
To make it look like a date for the user, you must assign a date format to the cell. Then the number is displayed as the date in the cell. (Excel automatically sets the date format if you enter something similar to a date in Excel.)
When you use ActiveWorkbook.ActiveSheet.Cells(x,y) , you create a Range object and call its default property, which is Value . Value has many magic built-in. In your case, it looks at the format of the cell and - the view of the date format - converts the internally stored number to the Variant the subtype date. When you display it using the message box, the following trick occurs. If the time is 0:00, the date is converted to a string without time. If the time is different from 0:00, it will be converted to a string with a date and time. The date format is taken from user settings. It does not depend on the date format that you have assigned to the Excel cell.
If you use Value2 instead of Value (for example, using the message field to display ActiveWorkbook.ActiveSheet.Cells(x,y).Value2 ), you will see an internal representation of the date, namely a number.
When you edit a cell, Excel uses another date format so that you can see and edit all parts of the date: year, month, day, and possibly hour, minute, and second. This is because the date format of your cell can only be limited by the name of the month.
Another difficulty is added by internationalization. Some date formats are not directly applied, but are a hint for using the date format from the current user settings. Therefore, the format is first replaced by a different date format, and then applied. In addition, certain parts of the date and time formats depend on user settings. And finally, date format models are translated (in English, yyyy stands for year, in it - jjjj ). When an Excel spreadsheet is saved, these formats are saved in English, so that the worksheet can be opened by the user and the Excel settings in any language. In your case, internationalization probably affects the date format used when editing the cell (setting month to day and using the 12-hour display with AM / PM looks like North America).
I don’t quite understand why Excel displays "7/22/2011 12:00:00 AM" (instead of "7/22/2011 0:00:00 AM"). I am sure your date / time has a time part of "0:00". But the extension number (as shown by Value2 ) will definitely tell you.