difference between dates calculated column

[robrobrobrob]

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 

[#2]

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

[#3]

@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.

[#4]

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