Forum Discussion

robrobrobrob's avatar
robrobrobrob
Copper Contributor
Dec 21, 2022

difference between dates calculated column

hi guys ... 

i have a sharepoint online list with a column named Begin that marks the creation time of a ticket

and i created another calculated column named End that timestamps the ticket when the column Status is Closed

This is the formula i used in Column End=IF(Status="Closed",TEXT(NOW(),"dd/mm/yyyy hh:mm"),IF(ISBLANK(Status),"","NULL")) ...  it works fine ( regardless it shows the time of different time zone )

then i created a column named Resolve Time that calculates the time between the column Begin and Column End and this is the formula=TEXT(End-Begin,"hh:mm") ... but it's not working ... i tried to create a column named End2 with type Date and Time and manually enter the date and it works so the formula is fine ... how can i fix this ?

 

here is a screenshot of the list looks like 

 

 

SharePoint Online
  • ganeshsanap's avatar
    ganeshsanap
    MVP
    Dec 22, 2022

    robrobrobrob Looking at your formula for "End" column, you have set "NULL" value for certain conditions. So, try using extended version of RobElliott's formula given below: 

     

    =IF(OR(ISBLANK([End]),ISBLANK([Begin]),[End]="NULL"),"",TEXT([End]-[Begin],"hh:mm"))

     

    Also, you have calculated "TEXT" value  for your "End" column. So, if above formula does not work for you, so have to convert the text value of "End" column in proper date format and use it in place of "[End]-[Begin]" section in above formula.

     

    Check functions supported for this: 

    1. DATE function 
    2. DATEVALUE function 

    Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

    • robrobrobrob's avatar
      robrobrobrob
      Copper Contributor
      Dec 22, 2022
      unfortunately ... it didn't work ... looks like it has got something to do with the type of Column ... because the same formula works when calculating between two columns of type Date and Time ... where as calculating a column Date & Time with a column of type Calculated it doesn't work
  • RobElliott's avatar
    RobElliott
    Silver Contributor
    Dec 22, 2022

    robrobrobrob try it with =IF(OR(ISBLANK([End]),ISBLANK([Begin])),"",TEXT([End]-[Begin],"hh:mm"))

     

    Rob
    Los Gallardos
    Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

Resources