Checking if Time Exceeds 24 Hours

Jimrny

Morning!

=IF(SETA@row = "", "", IF(FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) >= 12, IF(FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) > 12, (FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) - 12) + ":" + IF(LEN(MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) = 1, "0" + MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60), MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) + " PM", "12:" + IF(LEN(MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) = 1, "0" + MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60), MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) + " PM"), IF(FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) = 0, "12:" + IF(LEN(MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) = 1, "0" + MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60), MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) + " AM", FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) + ":" + IF(LEN(MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) = 1, "0" + MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60), MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) + " AM")))

The formula I am using checks for hours past 12 in my Return ETA. Hours that are within the first 24 hours are returned properly in Return ETA, however going past that 12hour mark, It doesn't reflect PM to AM properly.

I have the departure time converted into military time, and add the ETA minutes to that and convert it to hours:minutes am/pm to reflect in Return ETA

When I try to check past 24 hours, I'm returning an error instead. Where am I going wrong?

Jimrny

I figured it out!

=IF(ISBLANK(SETA@row), "" ,IF(FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) >= 24,(FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) - 24) + ":" + IF(LEN(MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) = 1, "0" + MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60), MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) + " AM", IF(FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) >= 12, IF(FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) > 12, (FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) - 12) + ":" + IF(LEN(MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) = 1, "0" + MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60),
MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)
) + " PM", "12:" + IF(LEN(MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) = 1, "0" + MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60), MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) + " PM"), IF(FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) = 0, "12:" + IF(LEN(MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) = 1, "0" + MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60), MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) + " AM", FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) + ":" + IF(LEN(MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) = 1, "0" + MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60), MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) + " AM")))
)

Jimrny

I figured it out!

=IF(ISBLANK(SETA@row), "" ,IF(FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) >= 24,(FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) - 24) + ":" + IF(LEN(MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) = 1, "0" + MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60), MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) + " AM", IF(FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) >= 12, IF(FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) > 12, (FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) - 12) + ":" + IF(LEN(MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) = 1, "0" + MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60),
MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)
) + " PM", "12:" + IF(LEN(MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) = 1, "0" + MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60), MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) + " PM"), IF(FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) = 0, "12:" + IF(LEN(MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) = 1, "0" + MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60), MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) + " AM", FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) + ":" + IF(LEN(MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) = 1, "0" + MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60), MOD((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row), 60)) + " AM")))
)