i have date field '01/09/2009 00:05:18'. i change the format and got '01/09/2009' but the cell value is still with hour. i want to cut it off permanently. how can i do it automaticlly?

Hi, Excel stores the date and time in the same variable. The date is the integer (whole number) part and the time is the decimal part. This means that you can't 'cut off' the time part. You can enter dates with a time part of zero which is midnight. .5 would be 12 noon, half-way through the day. As Excel stores dates and times as a number, (today at 06:00AM will be 40092.25 What you see in the cell depends on formatting. Select the cell and select Format Cells. In the number tab select Date and select a date format. You will see a date without any time. You can also use the custom format and create a date format that suits what you are doing. The other point is that the 00:05:18 in the cell is based on what was entered in the cell. When the date was entered, there was also a time entry, so you may need to manage how data is entered if you want the time part (decimal) to be .00 You can copy the cell using a formula that takes only the integer or date portion. If your date is in Cell A1, in A2 put =INT(A1). Format A2 as a date. (If you format it as a number you will see that the decimal part will be .00) Regards

thank you very much for your help. i didn't know i can use the INT function on date format. it helped me. thanks,