locked
Pivot table date formatting RRS feed

  • Question

  • Hello

    I've got a date value below coming from a cube and surfaced on a pivot table in excel. When I try to extract the month or run a formula 

    =IF(DATEVALUE(CONCATENATE("01/",MONTH(A1),"/",YEAR(A1)))<DATEVALUE(CONCATENATE("01/",MONTH(TODAY()),"/",YEAR(TODAY()))),0,1)

    on a column outside the pivot, I get an error as the one below

    Can some one advise how to format the cells, so I can run the date calculations.

    I've tried all the usual ways to format the cells, including Text to Columns, but nothing seems to work. I've tried taking the date value outside the Pivot (=A1) and formatting this, but it always formats as string.

    All suggestions welcome

    Thanks in advance 

    Roy

    Friday, February 1, 2019 4:33 PM

Answers

  • My Regional settings had changed. I've reset them and everything okay...
    • Marked as answer by ryand09 Monday, February 4, 2019 9:22 AM
    Monday, February 4, 2019 9:22 AM