Share via

Calculte 2 dates with Today date in case 2nd date not available

Wissam 61 Reputation points
2022-09-28T07:46:25.093+00:00

Hello, It sound complicated but I have 2 dates (Starting) and (Closing) I need to calculate the age between this 2 columns but also if not closed it show me the age till today. I used a formula but the return was #VALUE on the items that have no (Closing) date while the one that have (Closing) was successful.

Your support is appreciated

245461-image.png

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,300 questions
Accepted answer
  1. Renjie Sun-MSFT 2,856 Reputation points Microsoft Employee
    2022-09-30T09:44:09.45+00:00

    Dear @Wissam ,

    Thank you for your reply.

    I have I tested the formula you provided after splitting it and found that the problem is in the part of ‘Today’.
    When choosing ‘Date and time’ option in calculate column, the time with minutes and seconds of ‘Today’ is not accurate in the SharePoint Online.

    This answer mentions the problem of inaccurate output time of ‘Today’ formula, hope it could help you a little.

    Should you have any questions or concerns, please do feel free to contact me.

    Yours sincerely,
    Renjie Sun

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Wissam 61 Reputation points
    2022-09-29T06:38:16.34+00:00

    I had it working now, by getting the closed and today, but the return time is wired and not accurate and stopes at 12min for all.

    =IF(Closing<>"",DATEDIF(open,Closing,"d")&"days "&TEXT(open-Closing,"h")&"hrs "&TEXT(open-Closing,"mm")&"min",DATEDIF(open,TODAY(),"d")&"days "&TEXT(open-TODAY(),"h")&"hrs "&TEXT(open-TODAY(),"mm")&"min")

    0 comments