Hi all,
I was able to calculate the time between order date and collection date (collection date - order date), but I realized that the collection date/time can come before the order date/time. In that case, I like to subtract the collection date/time from the order date/time.How can I go about doing this?
Formulas I used to calculate the time between order date and collection date:
TAT - Order to Collection (Sec)=DaysBetween([Ordered Date-Time];[Collection Date-Time])*86400 + ( ToNumber(FormatDate([Collection Date-Time];"HH"))*3600 + ToNumber(FormatDate([Collection Date-Time];"mm"))*60 + ToNumber(FormatDate([Collection Date-Time];"ss")) ) - ( ToNumber(FormatDate([Ordered Date-Time];"HH"))*3600 + ToNumber(FormatDate([Ordered Date-Time];"mm"))*60 + ToNumber(FormatDate([Ordered Date-Time];"ss")) )
TAT - Order to Collection=FormatNumber(Floor([TAT - Order to Collection (Sec)]/86400);"00") + " " + "Days" + " " + FormatNumber(Floor(Mod([TAT - Order to Collection (Sec)] ;86400)/3600);"00") + ":" + FormatNumber(Floor(Mod(Mod([TAT - Order to Collection (Sec)] ;86400);3600)/60);"00")
-----------------------------------------------------------------------------------------------
I was thinking about ifelse statement like this, but it doesn't work probably because I'm not formatting.
=If([Collection Date-Time]<[Ordered Date-Time]) Then [Ordered Date-Time] - [Collection Date-Time] Else [Collection Date-Time]-[Ordered Date-Time]
Thank you for your help!