If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Time Format Problem
Hi
I run Excel 2K I have a column of dates that are downloaded from a mainframe. The date format of these are 18/08/2009 7:58:00 AM. I am trying to contruct a pivot table that looks at these dates and allows me to pick a date range for the pivot table. I have the formula to do this which I have used many times. The problem I have is because there is a time included in the download it does not allow my formula to work correctly. How can I reformat the date to exclude the time component. Or, is there a formula that I can use that strips the time component? I have tried a custom format which forces it to be displayed as 18/08/2009 in the cell however it still shows 18/08/2009 7:58:00 AM in the edit bar. Thanks John |
#2
|
|||
|
|||
Time Format Problem
Formatting only changes the *display*. It doesn't change the true underlying
value of the cell. You can strip off the time like this: A1 = 18/08/2009 7:58:00 AM =INT(A1) Format as Date -- Biff Microsoft Excel MVP "John Calder" wrote in message ... Hi I run Excel 2K I have a column of dates that are downloaded from a mainframe. The date format of these are 18/08/2009 7:58:00 AM. I am trying to contruct a pivot table that looks at these dates and allows me to pick a date range for the pivot table. I have the formula to do this which I have used many times. The problem I have is because there is a time included in the download it does not allow my formula to work correctly. How can I reformat the date to exclude the time component. Or, is there a formula that I can use that strips the time component? I have tried a custom format which forces it to be displayed as 18/08/2009 in the cell however it still shows 18/08/2009 7:58:00 AM in the edit bar. Thanks John |
#3
|
|||
|
|||
Time Format Problem
Excellent !
Once again....well done ! Thanks John "T. Valko" wrote: Formatting only changes the *display*. It doesn't change the true underlying value of the cell. You can strip off the time like this: A1 = 18/08/2009 7:58:00 AM =INT(A1) Format as Date -- Biff Microsoft Excel MVP "John Calder" wrote in message ... Hi I run Excel 2K I have a column of dates that are downloaded from a mainframe. The date format of these are 18/08/2009 7:58:00 AM. I am trying to contruct a pivot table that looks at these dates and allows me to pick a date range for the pivot table. I have the formula to do this which I have used many times. The problem I have is because there is a time included in the download it does not allow my formula to work correctly. How can I reformat the date to exclude the time component. Or, is there a formula that I can use that strips the time component? I have tried a custom format which forces it to be displayed as 18/08/2009 in the cell however it still shows 18/08/2009 7:58:00 AM in the edit bar. Thanks John |
#4
|
|||
|
|||
Time Format Problem
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "John Calder" wrote in message ... Excellent ! Once again....well done ! Thanks John "T. Valko" wrote: Formatting only changes the *display*. It doesn't change the true underlying value of the cell. You can strip off the time like this: A1 = 18/08/2009 7:58:00 AM =INT(A1) Format as Date -- Biff Microsoft Excel MVP "John Calder" wrote in message ... Hi I run Excel 2K I have a column of dates that are downloaded from a mainframe. The date format of these are 18/08/2009 7:58:00 AM. I am trying to contruct a pivot table that looks at these dates and allows me to pick a date range for the pivot table. I have the formula to do this which I have used many times. The problem I have is because there is a time included in the download it does not allow my formula to work correctly. How can I reformat the date to exclude the time component. Or, is there a formula that I can use that strips the time component? I have tried a custom format which forces it to be displayed as 18/08/2009 in the cell however it still shows 18/08/2009 7:58:00 AM in the edit bar. Thanks John |
Thread Tools | |
Display Modes | |
|
|