Skip to Content
0
Jun 02, 2020 at 07:20 PM

Adding ifelse statement to the time difference formula

53 Views Last edit Jun 01, 2020 at 06:27 PM 4 rev

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!