Custom Column in PQ

[sadath]

Hi,

can anyone please tell what is wrong in this

if ([DeliveryTime]-[SUBMISSION_TIME])<0 then [DeliveryTime]-[SUBMISSION_TIME]+1 else [DeliveryTime]-[SUBMISSION_TIME]

 

[MarcelBeug]

Assuming that your fields are of type time: if you subtract times, the result is a duration. So if you want to compare the result with a value, you need to use #duration , like this:

=if ([DeliveryTime]-[SUBMISSION_TIME])<[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=duration"]#duration[/URL] (0,0,0,0) then [DeliveryTime]-[SUBMISSION_TIME]+[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=duration"]#duration[/URL] (1,0,0,0) else [DeliveryTime]-[SUBMISSION_TIME]

 

[MarcelBeug]

Alternative solution:

=#time(0,0,0) + ([DeliveryTime] - [SUBMISSION_TIME]) - #time(0,0,0)

 

[sadath]

Thank You..