Checking if Time Exceeds 24 Hours
![Jimrny](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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.2K Get Help
- 360 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!