Calculating Time Values

Hi Team,

I am new to the community but have been a SS user since 2012. In some reagrds I am still a learner but in my process ability I am building amazing stuff. Let me just say it has transformed my world and clients I work with! I am getting to work with some real complicated setups and trying to translate that into a workable solution for my clients.

The time calculation formula is what I need to know more details about... I have read several community posts and and the comments seem so easy but when I try it myself I am just not winning... So i have seen the massive formula of over two thousand long and I saw another one that was shorter... Tried both and not sure if I need to change something in the formula...

I am not winning that is the bottom line... I have added a screen shot if someone can be a bit more accurate with how to pop in that formula to make it work please.

PS: This is the formula Travis posted on the community but when I copy and paste that into a the line, it does not work....

To use it, copy and paste it into a text editor and use FIND & REPLACE to add your cell references to it.

=INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) + ":" + IF(((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) - INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) * 60) < 10, "0") + ((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) - INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) * 60)

Time Calculation.jpeg

Find more posts tagged with

Formulas

Comments

Andrée Starå

Hi,

Here's my formula that is working great. You maybe need to change the ; to , with find and replace.

=IFERROR(INT((((IF(LEFT([Stop1]@row; FIND(":"; [Stop1]@row) - 1) = "12"; IF(OR(FIND("a"; [Stop1]@row) > 0; FIND("p"; [Stop1]@row) > 0); 0; 12); VALUE(LEFT([Stop1]@row; FIND(":"; [Stop1]@row) - 1))) + IF(FIND("p"; [Stop1]@row) > 0; 12)) * 60 + VALUE(MID([Stop1]@row; FIND(":"; [Stop1]@row) + 1; 2))) - ((IF(LEFT([Start1]@row; FIND(":"; [Start1]@row) - 1) = "12"; IF(OR(FIND("a"; [Start1]@row) > 0; FIND("p"; [Start1]@row) > 0); 0; 12); VALUE(LEFT([Start1]@row; FIND(":"; [Start1]@row) - 1))) + IF(FIND("p"; [Start1]@row) > 0; 12)) * 60 + VALUE(MID([Start1]@row; FIND(":"; [Start1]@row) + 1; 2)))) / 60) + ":" + IF(((((IF(LEFT([Stop1]@row; FIND(":"; [Stop1]@row) - 1) = "12"; IF(OR(FIND("a"; [Stop1]@row) > 0; FIND("p"; [Stop1]@row) > 0); 0; 12); VALUE(LEFT([Stop1]@row; FIND(":"; [Stop1]@row) - 1))) + IF(FIND("p"; [Stop1]@row) > 0; 12)) * 60 + VALUE(MID([Stop1]@row; FIND(":"; [Stop1]@row) + 1; 2))) - ((IF(LEFT([Start1]@row; FIND(":"; [Start1]@row) - 1) = "12"; IF(OR(FIND("a"; [Start1]@row) > 0; FIND("p"; [Start1]@row) > 0); 0; 12); VALUE(LEFT([Start1]@row; FIND(":"; [Start1]@row) - 1))) + IF(FIND("p"; [Start1]@row) > 0; 12)) * 60 + VALUE(MID([Start1]@row; FIND(":"; [Start1]@row) + 1; 2)))) - INT((((IF(LEFT([Stop1]@row; FIND(":"; [Stop1]@row) - 1) = "12"; IF(OR(FIND("a"; [Stop1]@row) > 0; FIND("p"; [Stop1]@row) > 0); 0; 12); VALUE(LEFT([Stop1]@row; FIND(":"; [Stop1]@row) - 1))) + IF(FIND("p"; [Stop1]@row) > 0; 12)) * 60 + VALUE(MID([Stop1]@row; FIND(":"; [Stop1]@row) + 1; 2))) - ((IF(LEFT([Start1]@row; FIND(":"; [Start1]@row) - 1) = "12"; IF(OR(FIND("a"; [Start1]@row) > 0; FIND("p"; [Start1]@row) > 0); 0; 12); VALUE(LEFT([Start1]@row; FIND(":"; [Start1]@row) - 1))) + IF(FIND("p"; [Start1]@row) > 0; 12)) * 60 + VALUE(MID([Start1]@row; FIND(":"; [Start1]@row) + 1; 2)))) / 60) * 60) < 10; "0") + ((((IF(LEFT([Stop1]@row; FIND(":"; [Stop1]@row) - 1) = "12"; IF(OR(FIND("a"; [Stop1]@row) > 0; FIND("p"; [Stop1]@row) > 0); 0; 12); VALUE(LEFT([Stop1]@row; FIND(":"; [Stop1]@row) - 1))) + IF(FIND("p"; [Stop1]@row) > 0; 12)) * 60 + VALUE(MID([Stop1]@row; FIND(":"; [Stop1]@row) + 1; 2))) - ((IF(LEFT([Start1]@row; FIND(":"; [Start1]@row) - 1) = "12"; IF(OR(FIND("a"; [Start1]@row) > 0; FIND("p"; [Start1]@row) > 0); 0; 12); VALUE(LEFT([Start1]@row; FIND(":"; [Start1]@row) - 1))) + IF(FIND("p"; [Start1]@row) > 0; 12)) * 60 + VALUE(MID([Start1]@row; FIND(":"; [Start1]@row) + 1; 2)))) - INT((((IF(LEFT([Stop1]@row; FIND(":"; [Stop1]@row) - 1) = "12"; IF(OR(FIND("a"; [Stop1]@row) > 0; FIND("p"; [Stop1]@row) > 0); 0; 12); VALUE(LEFT([Stop1]@row; FIND(":"; [Stop1]@row) - 1))) + IF(FIND("p"; [Stop1]@row) > 0; 12)) * 60 + VALUE(MID([Stop1]@row; FIND(":"; [Stop1]@row) + 1; 2))) - ((IF(LEFT([Start1]@row; FIND(":"; [Start1]@row) - 1) = "12"; IF(OR(FIND("a"; [Start1]@row) > 0; FIND("p"; [Start1]@row) > 0); 0; 12); VALUE(LEFT([Start1]@row; FIND(":"; [Start1]@row) - 1))) + IF(FIND("p"; [Start1]@row) > 0; 12)) * 60 + VALUE(MID([Start1]@row; FIND(":"; [Start1]@row) + 1; 2)))) / 60) * 60); 0)

I hope this helps you!

Best,

Andrée Starå - Workflow Consultant @ Get Done

server54451

Andree,

Thank you for replying, can you perhaps send me your email that I can share the file with you? I did as you said and not coming right. Not sure what I am doing wrong else.

My email is lucas@africapowersolutions.com

Sign in to join the conversation!

Quick Links

Trending discussions

Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…
INDEX COLLECT MIN
Desired result is to pull the Initiative Name where the Days to Due Date is the least (so, due the soonest); then to make an index for the Top 10 Initiatives Due Soonest Currently using this formula: =INDEX(COLLECT({Project Master - Initiative}, {Project Master - Days to Due}, MIN({Project Master - Days to Due})), 1) This…