Forum Discussion

lynnzrae's avatar
lynnzrae
Helper I
2 years ago
Solved

DATEDIFF between two dates or if one date isn't given DATEDIFF between one date and today

I am creating a new column to identify the cycle time for projects.

I want to create a formula so that I datediff from the start date to the complete date and if there is not a complete date (complete date is blank) I want to datediff from the start date to today.  Thank you for your help.

 

Project Start DateProject Complete DateProject Cycle Time
1/15/20241/31/2024 
2/9/2024  
3/5/20246/1/2024 
1/16/20242/27/2024 
6/5/2024  
DATEDIFF
  • BeaBF's avatar
    BeaBF
    2 years ago

    lynnzrae Here the calculated column:

    Project Cycle Time_BBF =
    IF(
        ISBLANK('Table'[Project Complete Date]),
        DATEDIFF('Table'[Project Start Date], TODAY(), DAY),
        DATEDIFF('Table'[Project Start Date], 'Table'[Project Complete Date], DAY)
    )
    output:

     

    BBF

2 Replies

  • BeaBF's avatar
    BeaBF
    Super User
    2 years ago

    lynnzrae Here the calculated column:

    Project Cycle Time_BBF =
    IF(
        ISBLANK('Table'[Project Complete Date]),
        DATEDIFF('Table'[Project Start Date], TODAY(), DAY),
        DATEDIFF('Table'[Project Start Date], 'Table'[Project Complete Date], DAY)
    )
    output:

     

    BBF