Forum Discussion

Rsanjuan's avatar
Rsanjuan
Advocate 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 Until Due Date = ('Account  Planning'[Due Date]-'Account  Planning'[TodayCol])*1

 

The two due dates that I have is 6/23/17 and 5/30/17.  One is overdue.  It should return 4 and -20 respectively.

 

However, it is returning the values as 2092 and -10460?  Both fields are in date format.  Am I doing something wrong?

 

 

 

 

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]))

     

4 Replies

  • Chihiro's avatar
    Chihiro
    Solution 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
      Advocate 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
        Solution 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]))

         

  • srhoag's avatar
    srhoag
    Regular Visitor
    8 years ago

    I have a simple calculation to show how many days until a shipment of product is expected to arrive.   The formula I used is:  Expected Arrival in days = int('FA Live$Purchase Line'[Expected Receipt Date]-Today()).    It works fine EXCEPT for shipments arriving today.   Then it shows 3 days rather than 0 days.

     

    I found that the INT(Today()) = 43039  (October 31,2017)

    I found that the INT('FA Live$Purchase Line'[Expected Receipt Date]) for a shipment due today = 43042.

     

    This is confusing to me.   Any help would be appreciated.