Forum Discussion

Rsanjuan's avatar
Rsanjuan
Icon for Advocate III rankAdvocate III
9 years ago
Solved

Calculating Days Between a Date and Today

Hi,   I'm trying to calculate the number of days between 1 date field and today.   I created a column for Today = DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))   Then did this:  Remaining Days Unti...
Need Help
  • Chihiro's avatar
    Chihiro
    9 years ago

    Yes, it will likely return negative INT value for today's date, if there are blanks.

    You can nest the function in IF() function to return blank if [Due Date] is blank.

     

    =IF(ISBLANK('Account  Planning'[Due Date]),BLANK(),INT('Account  Planning'[Due Date]-'Account  Planning'[TodayCol]))

     

Chihiro's avatar
Chihiro
Icon for Solution Sage rankSolution Sage
9 years ago

Try using...

=INT('Account  Planning'[Due Date]-'Account  Planning'[TodayCol])

Also, instead of using DATE() & NOW() for Today column. You can just use TODAY().

 

EDIT: If above doesn't fix it. Try first converting each of date column to integer using INT(COLUMN).

If the value does not fall in range of 42736 to 43100 for year 2017. Then there's something off with your date column(s).

  • Rsanjuan's avatar
    Rsanjuan
    Icon for Advocate III rankAdvocate III
    9 years ago

    Chihiro

     

    Thanks.  For some reason, it's not working for me.  There are some rows that have a "null" in the Due Date field.  Will that cause it?

     

     

    • Chihiro's avatar
      Chihiro
      Icon for Solution Sage rankSolution Sage
      9 years ago

      Yes, it will likely return negative INT value for today's date, if there are blanks.

      You can nest the function in IF() function to return blank if [Due Date] is blank.

       

      =IF(ISBLANK('Account  Planning'[Due Date]),BLANK(),INT('Account  Planning'[Due Date]-'Account  Planning'[TodayCol]))