Checking if Time Exceeds 24 Hours
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?
Best Answer
-
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")))
)
Answers
-
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")))
)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!