Forum Discussion

hassan41's avatar
hassan41
Copper Contributor
Oct 20, 2021

Column formula is not working

Hi, 

 

Report dataI am not a data person but need to create the report. I think the solution to problem will be simple but i dont know it. 

I have simple ms form data in excel sheet. where i have created a pivot table for reporting. 

When a new response is submitted. The formula applied to a month column is not working and i am getting random error data in that column. although the week column formula is working accurately for the same data. The formula's are Month =MONTH([@[Completion time]]) and Week =WEEKNUM([@[Completion time]])

 

Please see the attached screen shot and provide a solution.  

excel

8 Replies

Sort By
    • hassan41's avatar
      hassan41
      Copper Contributor
      Oct 20, 2021
      Hi Hans, Thanks for response. that were i am facing an issue. i want the every new response/data to automatically get the column formula. i have implemented it the same way. the week column formula is working for every new entry and the month column one isn,t.
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Oct 20, 2021

        hassan41 

        1) In File > Options > Proofing > AutoCorrect Options... > AutoFormat As You Type tab, make sure that the check box "Fill formulas in tables to create calculated columns" is ticked.

        2) Clear the colum n in the table (except for the header of course), then enter the formula =MONTH([@[Completion time]]) in the cell below the header. Excel should automatically fill the entire table column.

Resources