Forum Discussion

Ashish_kumar12's avatar
Ashish_kumar12
Icon for Helper I rankHelper I
7 years ago
Solved

Error:An argument of function 'DATE' has the wrong data type or the result is too large or too small

Hi All,

I am trying make an new column where i want to compare today's week num and year with Date of birth of candidates (i.e 18-sep-1989 with today's date(18-Sep-2019). However i am getting an error which is:

 

"An argument of function 'DATE' has the wrong data type or the result is too large or too small "

Formula which i am using is :

DOB_View = IF(WEEKNUM(DATE(YEAR(TODAY()),MONTH(Union_Table[field_date_of_birth]),DAY(Union_Table[field_date_of_birth])),2)=WEEKNUM(TODAY(),2),"This Week","All Others")
 
Can anyone help me with correct solution ?
Thanks
Ashish 
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Ashish_kumar12 

    This error is because there are blank value in field_date_of_birth column, just adjust your formula as below:

    DOB_View = IF(ISBLANK(Union_Table[field_date_of_birth])=FALSE(),
IF (
    WEEKNUM (
        DATE ( YEAR ( TODAY () ), MONTH ( Union_Table[field_date_of_birth] ), DAY ( Union_Table[field_date_of_birth] ) ),
        2
    )
        = WEEKNUM ( TODAY (), 2 ),
    "This Week",
    "All Others"
)
, "All Others")

    Result:

    BeforeAfter

     

    Best Regards,

    Lin

  • Ashish_kumar12's avatar
    Ashish_kumar12
    7 years ago

    Hi Lin,

     

    Thank you for your help. It works and i managed to make this column.

     

    Regards,

    Ashish

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    7 years ago

    I tried the same on a date field and it worked. Just check DOB is of data type or not

     

    Week No Compare = if(WEEKNUM(date(YEAR(TODAY()),MONTH(Sales[Sales Date]),day(Sales[Sales Date])),2)=WEEKNUM(TODAY(),2) ,"This Week","All Other")

    Or the position of parenthesis

     

    • Ashish_kumar12's avatar
      Ashish_kumar12
      Icon for Helper I rankHelper I
      7 years ago

      Hi Amit,

       

      Thank you for your revert.

       

      Do you changed the data type from text to Date?

       

      As it still giving me the same errors. Please see below screenshot for the same 

       

      • v-lili6-msft's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        hi, Ashish_kumar12 

        This error is because there are blank value in field_date_of_birth column, just adjust your formula as below:

        DOB_View = IF(ISBLANK(Union_Table[field_date_of_birth])=FALSE(),
IF (
    WEEKNUM (
        DATE ( YEAR ( TODAY () ), MONTH ( Union_Table[field_date_of_birth] ), DAY ( Union_Table[field_date_of_birth] ) ),
        2
    )
        = WEEKNUM ( TODAY (), 2 ),
    "This Week",
    "All Others"
)
, "All Others")

        Result:

        BeforeAfter

         

        Best Regards,

        Lin