Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    I am trying to drop the time part of a cell in Excel.

    I have an input file which is loaded into excel, the field has dd/mm/yyyy hh:mm:sss, so I can format the cell to show dd/mm/yyyy which is fine but when a reference the field in a pivot table with the getpivot command that field is referenced as Date(yyyy,mm,dd) + Time(hh,mm,ss).

    This makes it imposible for me to pick up data for a specific day in the pivot table.

    If I remove the Time part I then get a #Ref! in the field which to be expected.

    Any clues.

    Regards

    Mike

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    This makes it imposible for me to pick up data for a specific day in the pivot table.

    If I remove the Time part I then get a #Ref! in the field which to be expected.

    Any clues.

    Regards

    Mike
    [/quote]

    Mike:

    You can try this.

    1. For the Cell that shows the results of the GetPivot command change its format to number with 2 decimals. Lets say that cell is B10
    2. The in another Cell use this formula =(Date(Year(B10),Month(B10),Day(B10))
    3. This will give a a whole number that represents the Excel number for that day.
    4. You can then change the format of the cell with the formula to the Date format you desire. This should allow you to then perform date computations

    Hope this helps

    Tom Duthie

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Can you group the column and pull it out of the grouped data?

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='duthiet' post='793020' date='11-Sep-2009 18:48']This makes it imposible for me to pick up data for a specific day in the pivot table.

    If I remove the Time part I then get a #Ref! in the field which to be expected.

    Any clues.

    Regards

    Mike


    Mike:

    You can try this.

    1. For the Cell that shows the results of the GetPivot command change its format to number with 2 decimals. Lets say that cell is B10
    2. The in another Cell use this formula =(Date(Year(B10),Month(B10),Day(B10))
    3. This will give a a whole number that represents the Excel number for that day.
    4. You can then change the format of the cell with the formula to the Date format you desire. This should allow you to then perform date computations

    Hope this helps

    Tom Duthie[/quote]

    Thanks Tom,

    This approach worked.

    Regards

    Mike

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='793054' date='11-Sep-2009 23:24']Can you group the column and pull it out of the grouped data?

    Steve[/quote]

    Thanks Steve,

    For some reason I could not get the Grouping option to work, but Tom's appoach solved my problem.

    Regards

    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •